Re: partition queries hitting all partitions even though check key is specified

On 9/2/09 8:59 AM, "Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:

> On Wed, 2009-09-02 at 09:39 -0600, Kevin Kempter wrote:
>
>>>
>>> You sure you remembered those fiddly little casts everywhere?
>>> (Frankly, declaring "time" as integer and not timestamp here strikes
>>> me as utter lunacy.) What PG version are you using?
>>>
>>> regards, tom lane
>>
>
> As far as I know constraint exclusion doesn't work with date_part or
> extract().
>
> The following caveats apply to constraint exclusion:
>
> * Constraint exclusion only works when the query's WHERE clause
> contains constants. A parameterized query will not be optimized,
> since the planner cannot know which partitions the parameter
> value might select at run time. For the same reason, "stable"
> functions such as CURRENT_DATE must be avoided.
>
> http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html
>
> Or did I miss something?

I've only ever seen it work for constants. Partitioning by date works fine
as far as I know no matter how you set the constraint rule up (functions are
fine here, but slower). But the query itself has to submit a constant in
the WHERE clause. Prepared statements and parameterization on the query
won't work either.
For dates, literals like 'yesterday' work, but function equivalents don't.
Basically if the planner interprets the where condition on the column as a
constant (even if resolving that constant calls a function, such as
'yesterday') it will work. Otherwise, it won't.

>
> Joshua D. Drake
>
> --
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
> Consulting, Training, Support, Custom Development, Engineering
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>