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

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