Re: Exclusion constraints with time expressions

On 2012-11-06, Kevin Grittner <kgrittn(at)mail(dot)com> wrote:
>
> 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"?

In 8.4 I've seen scans of excluded partitions, fortunately they are index scans
which hit an index and find no rows on the first index page, so not too painful,
but there's still the locking overhead. I've learned to precompute the timestamptz
constants.

--
⚂⚃ 100% natural