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

Reply via email to