From: | Ben Chobot <bench(at)silentmedia(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | constraint exclusion with a tsrange type |
Date: | 2018-09-12 23:41:30 |
Message-ID: | EC8A473F-BA09-4AE7-9D61-5A1027FF0852@silentmedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hey everybody, I'm having trouble getting constraint exclusion to work on a table partitioned with a tsrange type. I've distilled it down to this:
create table t (
id serial primary key,
observed_window tsrange not null
);
create index t_window on t(observed_window);
create table p1 (like t including all);
alter table p1 add check ( tsrange('2018-1-1','2019-2-1') @> observed_window);
alter table p1 inherit t;
create table p2 (like t including all);
alter table p2 inherit t;
alter table p2 add check ( tsrange('2018-2-1','2019-3-1') @> observed_window);
# explain select * from t where tsrange('2018-1-5','2018-1-6') @> observed_window;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Append (cost=0.00..51.75 rows=13 width=36)
-> Seq Scan on t (cost=0.00..0.00 rows=1 width=36)
Filter: ('["2018-01-05 00:00:00","2018-01-06 00:00:00")'::tsrange @> observed_window)
-> Seq Scan on p1 (cost=0.00..25.88 rows=6 width=36)
Filter: ('["2018-01-05 00:00:00","2018-01-06 00:00:00")'::tsrange @> observed_window)
-> Seq Scan on p2 (cost=0.00..25.88 rows=6 width=36)
Filter: ('["2018-01-05 00:00:00","2018-01-06 00:00:00")'::tsrange @> observed_window)
(7 rows)
I would have expected that postgres could reason that, because I'm asking for an observed_window that fits within the tsrange (2018-1-5,2018-1-6), no matter what it was, it would not be found in p2. Obviously postgres says I'm wrong, but I don't know why?
Constraint exclusion is set to "partition".
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2018-09-12 23:52:50 | Re: PG9.1 migration to PG9.6, dump/restore issues |
Previous Message | Tom Lane | 2018-09-12 20:53:46 | Re: Code of Conduct plan |