Albe Laurenz wrote:
> Thomas Munro wrote:
>> I am using 9.1.6, and I've set up a partitioned table as described
>> in the manual, with partitions based on a timestamptz column
>> called 'time'. The exclusion constraints work nicely when I select
>> ranges of times with literal constants. But why would a WHERE
>> clause like the following not benefit from exclusion constraints?
>>
>> time > TIMESTAMPTZ '2012-11-03 00:00:00Z' + INTERVAL '24 hours'
>>
>>
>> Isn't that expression on the right reducible to a constant up
>> front? Obviously I can use a host language to do the arithmetic
>> and provide a constant, but I am curious to understand why that
>> wouldn't be constant-folded.
>
> I think the problem is that this + operator is implemented
> by the function "timestamptz_pl_interval", which is STABLE
> but not IMMUTABLE.
>
> I am not sure why this function cannot be IMMUTABLE, it
> seems to me that it should be.
For TIMESTAMP WITHOUT TIME ZONE it couldn't be IMMUTABLE, because the
result would be based on the time zone setting of the client
connection; but adding a fixed interval to a UTC time to get a UTC
time seems pretty immutable to me. That said, I'm not sure why STABLE
wouldn't be good enough for such an optimization, if it were
supported at all. I don't think we evaluate such expressions before
developing the plan, though.
If you run EXPLAIN ANALYZE on one of the queries involved, does it
actually perform the scan of partitions which can be skipped at
run-time, or does it show "never executed"?
-Kevin