Re: Constraint exclusion on tables partitioned over range types

2014-08-22 14:34 GMT-03:00 Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>:

> Hello,
>
> we are setting up a partitioned table based on tstzrange in PG 9.3,
> something like:
>
> create table offer (
> during tstzrange not null,
> ...
> constraint virtual check (false) no inherit
> );
>
> create table offer_201408 (
> check (during <@ '[2014-08-01Z,2014-09-01Z)'::tstzrange)
> ) inherits (offer);
>
> create table offer_201409 (
> check (during <@ '[2014-09-01Z,2014-10-01Z)'::tstzrange)
> ) inherits (offer);
>
> I haven't found a way to make the planner constraint exclusion kicking in:
>
> =# explain select * from offer where during @>
> '2014-08-03'::timestamptz;
>
> Append (cost=0.00..27.25 rows=3 width=248)
> -> Seq Scan on offer (cost=0.00..0.00 rows=1 width=248)
> Filter: (during @> '2014-08-03 00:00:00+01'::timestamp
> with time zone)
> -> Seq Scan on offer_201408 (cost=0.00..13.62 rows=1 width=248)
> Filter: (during @> '2014-08-03 00:00:00+01'::timestamp
> with time zone)
> -> Seq Scan on offer_201409 (cost=0.00..13.62 rows=1 width=248)
> Filter: (during @> '2014-08-03 00:00:00+01'::timestamp
> with time zone)
>
> Similar results using tztzrange OP tstzrange operators with OP in &&, @>,
> <@.
>
> Seqscans aside, as these tables are empty so they are expected, I
> wonder if there is a way to organize the operators used in the
> constraints and the ones used in the query so that the query planner
> would be able to exclude some of the tables before querying them, as
> is easy to do implementing range constraints on the base tstz type and
> its ordering operators.
>
> It would be also nice if the always failing constraint on the base
> table could suggest the planner that there is no record to be found
> there: I think this would be easier to implement but not as useful as
> for the ranges.
>
>
I guess you should write down a feature request on the hackers list.
tsrange should be included also on that request.

I tested on the beta version and it does not work either.

--
--
Emanuel Calvo http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services