Re: Constraint exclusion not working - need different constraints?

"=?utf-8?b?RG91ZyBHb3JsZXk=?=" <doug(at)gorley(dot)ca> writes:
> On Wed, 27 May 2015 18:21:58 -0400, Tom Lane wrote:
> You'd be much better off using child-table constraints like
> "timestamp" >= '2015-01-01' AND "timestamp" < '2015-01-08'
> because the planner can reason about them. But I'm afraid the age()
> technique still loses.

> Thanks Tom, I will re-work the constraints to use static dates.
>
> If I do that, will I be able to use age() (or some other technique) to
> apply constraint exclusion when running a query asking, "show me
> all records where the timestamp is within the last 24 hours"?

Basically the query will need to look like

WHERE "timestamp" >= 'timestamp-constant'

or the planner won't be able to eliminate any partitions.

People have occasionally resorted to lying to the planner in order to get
this result without doing timestamp arithmetic on the client side. That
is, something like

create function ago(interval) returns timestamp as
'select localtimestamp - $1'
language sql immutable;

select ... where "timestamp" >= ago('24 hours');

Labeling this function immutable is a plain lie, and if you use it in any
context other than an interactive query submitted for immediate execution,
you'll deserve the pain you'll get ;-). But within that context, it's just
what you want that the function gets folded to a constant immediately;
that happens soon enough that the WHERE clause looks like "timestamp" >=
'timestamp-constant' for the purposes of constraint exclusion.

regards, tom lane