Обсуждение: constraint exclusion with a tsrange type

Поиск
Список
Период
Сортировка

constraint exclusion with a tsrange type

От
Ben Chobot
Дата:
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".


Re: constraint exclusion with a tsrange type

От
Tom Lane
Дата:
Ben Chobot <bench@silentmedia.com> writes:
> Hey everybody, I'm having trouble getting constraint exclusion to work on a table partitioned with a tsrange type.
I'vedistilled 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;

Nope, sorry, there's no logic in there about ranges.  You'd have to
break this down into something involving simple timestamp comparison
operators for constraint exclusion to be able to prove anything.

Might be a reasonable future extension, perhaps...

            regards, tom lane