Constraint exclusion on tables partitioned over range types

From: Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Constraint exclusion on tables partitioned over range types
Date: 2014-08-22 17:34:12
Message-ID: CA+mi_8Z3x6bSWZnKYJBuU9FsN7e-HLkDW8mVW0Q9-OKV3KE-dg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Thank you very much,

-- Daniele

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hubert depesz lubaczewski 2014-08-22 17:35:14 Re: deadlock in single-row select-for-update + update scenario? How could it happen?
Previous Message John R Pierce 2014-08-22 17:29:14 Re: deadlock in single-row select-for-update + update scenario? How could it happen?