Re: Constraint exclusion on tables partitioned over range types - Mailing list pgsql-general

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.