2014-08-22 14:34 GMT-03:00 Daniele Varrazzo <daniele.varra...@gmail.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
