Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E5E44633085 for ; Wed, 3 Feb 2010 11:54:47 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 39695-01 for ; Wed, 3 Feb 2010 15:54:27 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f225.google.com (mail-fx0-f225.google.com [209.85.220.225]) by mail.postgresql.org (Postfix) with ESMTP id CD97363290A for ; Wed, 3 Feb 2010 11:54:36 -0400 (AST) Received: by fxm25 with SMTP id 25so103750fxm.34 for ; Wed, 03 Feb 2010 07:54:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=oXJrFc3JxISCiPo9wVI60qi5K5kIeENgxVzDiBou9tQ=; b=Z10QlNTu3h5GL+GrO1TLXgIJr9W88EAwXkfbDILsdYOdb8DSmm+rQfncqDaZGstr0L yKpuYzslGdVqGi81g0O3sri2spkIfkhBmvSaDBFtpf30wgxDjyFRwjZF9+pO+p3HGesS krNjt0798SK1HsKIDH9S3xwQbbalQZMvqn7/o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=S/Q+qQQ0YBYp+a0hLwsQ2wzBCpWdeTS1F25k6DBOw7I81mIa6h9L5dvECJszd0KDIH 5vfTKamhlS9+6di7xgDaIo/O8Xbw2/z+blsBmPVSlu3e2Fesjs9fcafo38g7n6pU6/0l MV32/wtc7rwpRoKwSXdR2IjWViZEgrECogtAw= MIME-Version: 1.0 Received: by 10.216.86.80 with SMTP id v58mr1754281wee.40.1265212474009; Wed, 03 Feb 2010 07:54:34 -0800 (PST) In-Reply-To: <5002.1265156054@sss.pgh.pa.us> References: <5002.1265156054@sss.pgh.pa.us> From: Nikolas Everett Date: Wed, 3 Feb 2010 10:54:13 -0500 Message-ID: Subject: Re: queries with subquery constraints on partitioned tables not optimized? To: Tom Lane Cc: "Davor J." , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d99ef51428ed047eb43c87 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.52 tagged_above=-10 required=5 tests=AWL=-0.078, BAYES_00=-2.599, HTML_MESSAGE=0.001, SUBJECT_FUZZY_TION=0.156 X-Spam-Level: X-Archive-Number: 201002/60 X-Sequence-Number: 37481 --0016e6d99ef51428ed047eb43c87 Content-Type: text/plain; charset=ISO-8859-1 On Tue, Feb 2, 2010 at 7:14 PM, Tom Lane wrote: > "Davor J." writes: > > Now, if one takes a subquery for "1", the optimizer evaluates it first > > (let's say to "1"), but then searches for it (sequentially) in every > > partition, which, for large partitions, can be very time-consuming and > goes > > beyond the point of partitioning. > > No, the optimizer doesn't "evaluate it first". Subqueries aren't ever > assumed to reduce to constants. (If you actually do have a constant > expression, why don't you just leave out the word SELECT?) > > regards, tom lane If you don't have a constant expression then you can either explicitly loop in the calling code or a function or you could index the key in all the subtables. The index isn't really optimal but it gets the job done. Nik --0016e6d99ef51428ed047eb43c87 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Tue, Feb 2, 2010 at 7:14 PM, Tom Lane= <tgl@sss.pgh.pa.= us> wrote:
"Davor J." <DavorJ@live.com> writes:
> Now, if one takes a subquery for "1", the optimizer evaluate= s it first
> (let's say to "1"), but then searches for it (sequential= ly) in every
> partition, which, for large partitions, can be very time-consuming and= goes
> beyond the point of partitioning.

No, the optimizer doesn't "evaluate it first". =A0Subqu= eries aren't ever
assumed to reduce to constants. =A0(If you actually do have a constant
expression, why don't you just leave out the word SELECT?)

=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane

If you don't have a constant expression then you= can either explicitly loop in the calling code or a function or you could = index the key in all the subtables. =A0The index isn't really optimal b= ut it gets the job done.

Nik
--0016e6d99ef51428ed047eb43c87--