Thread: Constraint exclusion not working - need different constraints?
Good day, I'm attempting to implement table partitioning with constraint exclusions, but I'm not seeing the planner limit the plan to an appropriate set of child tables. I'm wondering if the functions in my constraints are making the exclusion impossible. My constraints look like this: ALTER TABLE response_data.queries_response_2015w23 ADD CONSTRAINT queries_response_2015w23_timestamp_check CHECK ( date_part('year'::text, "timestamp"::timestamp without time zone) = 2015::double precision AND date_part('week'::text, "timestamp"::timestamp without time zone) = 23::double precision ); And my query looks like this: explain select * from public.queries_response where age("timestamp"::timestamp) < '24 hours'; http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html gives this caveat: "Constraint exclusion only works when the query's WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time." Will I likely need to replace the date_part functions with actual dates to make this work? Thanks, -- Doug Gorley | doug@gorley.ca
"=?utf-8?b?RG91ZyBHb3JsZXk=?=" <doug@gorley.ca> writes: > I'm attempting to implement table partitioning with constraint exclusions, > but I'm not seeing the planner limit the plan to an appropriate set of > child tables. I'm wondering if the functions in my constraints are making > the exclusion impossible. > My constraints look like this: > ALTER TABLE response_data.queries_response_2015w23 > ADD CONSTRAINT queries_response_2015w23_timestamp_check > CHECK ( > date_part('year'::text, "timestamp"::timestamp without time zone) = 2015::double precision AND > date_part('week'::text, "timestamp"::timestamp without time zone) = 23::double precision > ); > And my query looks like this: > explain select * from public.queries_response where age("timestamp"::timestamp) < '24 hours'; > > http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html gives this caveat: > "Constraint exclusion only works when the query's WHERE clause contains > constants (or externally supplied parameters). For example, a comparison > against a non-immutable function such as CURRENT_TIMESTAMP cannot be > optimized, since the planner cannot know which partition the function > value might fall into at run time." > > Will I likely need to replace the date_part functions with actual dates to > make this work? Well, you can't make it work like that, for sure. The planner has no clue that there's any connection between age() and date_part(). And if it did fully understand that relationship, it still would refuse to do constraint exclusion in this example, because the age() constraint is current-timestamp-dependent. It couldn't assume that now() when executing the query is the same as it was when planning the query, so it wouldn't know which partition to select. Worse still, if I'm right in guessing that the timestamp column is timestamp WITH time zone (if it isn't, why are you bothering with the casts?) then the check constraints themselves aren't immutable, because their effective behavior depends on the current setting of TimeZone. So the planner will refuse to make any deductions at all with them. 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. regards, tom lane
On Wed, 27 May 2015 18:21:58 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Doug Gorley" <doug@gorley.ca> writes:
> I'm attempting to implement table partitioning with constraint exclusions,
> but I'm not seeing the planner limit the plan to an appropriate set of
> child tables. I'm wondering if the functions in my constraints are making
> the exclusion impossible.
> My constraints look like this:
> ALTER TABLE response_data.queries_response_2015w23
> ADD CONSTRAINT queries_response_2015w23_timestamp_check
> CHECK (
> date_part('year'::text, "timestamp"::timestamp without time zone) = 2015::double precision AND
> date_part('week'::text, "timestamp"::timestamp without time zone) = 23::double precision
> );
> And my query looks like this:
> explain select * from public.queries_response where age("timestamp"::timestamp) < '24 hours';
>
> http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html gives this caveat:
> "Constraint exclusion only works when the query's WHERE clause contains
> constants (or externally supplied parameters). For example, a comparison
> against a non-immutable function such as CURRENT_TIMESTAMP cannot be
> optimized, since the planner cannot know which partition the function
> value might fall into at run time."
>
> Will I likely need to replace the date_part functions with actual dates to
> make this work?
Well, you can't make it work like that, for sure. The planner has no clue
that there's any connection between age() and date_part(). And if it did
fully understand that relationship, it still would refuse to do constraint
exclusion in this example, because the age() constraint is
current-timestamp-dependent. It couldn't assume that now() when executing
the query is the same as it was when planning the query, so it wouldn't
know which partition to select.
Worse still, if I'm right in guessing that the timestamp column is
timestamp WITH time zone (if it isn't, why are you bothering with the
casts?) then the check constraints themselves aren't immutable, because
their effective behavior depends on the current setting of TimeZone.
So the planner will refuse to make any deductions at all with them.
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.
regards, tom lane
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"?
Thanks,
Doug
"=?utf-8?b?RG91ZyBHb3JsZXk=?=" <doug@gorley.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