DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=vLYNowYyN2HwQKjpNPyLukeSzefc3omWQKHPGVNaQ9GXtTdNLUapB0qC8RDVp+i3hw
	FnaXOvCFY9de47CkdRN9k28YkwTTBGyYT/m4h0oSHC1hYgp3Dx97NTg8d0ei2Mkektyd
	ioELIXYvDJodm+xsD6e/gWg+LlXdC74TM3Hxk=
MIME-Version: 1.0
In-Reply-To: <f2fd819a1003011129h2222281fqa67bc45e051c6dd8@mail.gmail.com>
References: <f2fd819a1003011129h2222281fqa67bc45e051c6dd8@mail.gmail.com>
Date: Thu, 4 Mar 2010 17:40:02 -0500
Message-ID: <603c8f071003041440r2ec69a9dw91153f35ab2b3d53@mail.gmail.com>
Subject: Re: partition pruning
From: Robert Haas <robertmhaas@gmail.com>
To: Anj Adu <fotographs@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Mon, Mar 1, 2010 at 2:29 PM, Anj Adu <fotographs@gmail.com> wrote:
> When I use intervals in my query e.g =A0col1 between current_timestamp -
> interval '10 days' and current_timestamp...the optimizer checks ALL
> partitions =A0whereas if I use =A0 col1 between 2 hardcoded dates..only
> the applicable partitions are scanned.

Yep.  This is one example of a more general principle:
constant-folding happens before planning, but anything more complex
has to wait until execution time.  So the plan can't take into account
the value of current_timestamp in forming the plan.

Unfortunately I don't think there's really any easy way around this:
you have to do select current_timestamp, current_timestamp - interval
'10 days' first and then build & execute a new query.

...Robert