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

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Kevin Kempter <kevink(at)consistentstate(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: partition queries hitting all partitions even though check key is specified
Date: 2009-09-02 15:22:19
Message-ID: 407d949e0909020822g79acd758ub078415cc52de374@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Wed, Sep 2, 2009 at 4:05 PM, Kevin Kempter<kevink(at)consistentstate(dot)com> wrote:
> explain select * from pwreport.url_hits where "time" > extract('epoch' from
> timestamp '2009-08-12 00:00:00')::int4;
>

Hm. Actually I would have thought this would work. You're using
"timestamp" which defaults to without timezone and
date_part(text,timestamp) is marked immutable. So the condition in the
whree clause is being inlined at plan time so it's just a simple
comparison against an integer. That does appear to be successfully
happening.

I think what's happening is that the constraints are not being inlined
and the planner is not inlining them before comparing them to the
where clause. I wonder if this worked in the past or not.

You could make things work by defining your constraints to use the
integer results of those expressions explicitly. You could even do
write a simple perl script (or insert favourite scripting language) to
generate the constraint definitions from timestamps if you wanted.

--
greg
http://mit.edu/~gsstark/resume.pdf

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Pat Chan 2009-09-02 15:29:14 pg_stat_activity.current_query explanation?
Previous Message Tom Lane 2009-09-02 15:19:20 Re: partition queries hitting all partitions even though check key is specified