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: 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". |