Postgres 11.9: Query is searching all partitions for partition by tstzrange-CodePudding

I have partitioned a table based on column of type tstzrange

CREATE TABLE schedule_slots
(
    id                     int8        NOT NULL DEFAULT nextval('schedule_slots_id_seq'::regclass),
    time_range             tstzrange   NOT NULL,
    CONSTRAINT schedule_slots_pkey2 PRIMARY KEY (id, time_range)
) partition by range(time_range);

CREATE
INDEX time_range_idx2 ON schedule_slots USING gist (time_range);

Created below partitions :

CREATE TABLE schedule_slots_part1 PARTITION OF schedule_slots
    FOR VALUES FROM ('[2022-06-01 00:00:00-07,2022-06-01 23:59:59-07)') to ('[2022-09-01 00:00:00-07,2022-09-01 23:59:59-07)');

CREATE TABLE schedule_slots_part2 PARTITION OF schedule_slots
    FOR VALUES FROM ('[2022-09-01 00:00:00-07,2022-09-01 23:59:59-07)') to ('[2022-12-01 00:00:00-07,2022-12-01 23:59:59-07)');   

CREATE TABLE schedule_slots_part3 PARTITION OF schedule_slots
    FOR VALUES FROM ('[2022-12-01 00:00:00-07,2022-12-01 23:59:59-07)') to ('[2023-02-01 00:00:00-07,2023-02-01 23:59:59-07)');    

CREATE TABLE schedule_slots_part4 PARTITION OF schedule_slots
    FOR VALUES FROM ('[2023-02-01 00:00:00-07,2023-02-01 23:59:59-07)') to ('[2023-05-01 00:00:00-07,2023-05-01 23:59:59-07)'); 
   

 CREATE TABLE schedule_slots_part5 PARTITION OF schedule_slots
    FOR VALUES FROM ('[2023-05-01 00:00:00-07,2023-05-01 23:59:59-07)') to ('[2023-08-01 00:00:00-07,2023-08-01 23:59:59-07)'); 

    CREATE TABLE schedule_slots_part6 PARTITION OF schedule_slots
    FOR VALUES FROM ('[2023-08-01 00:00:00-07,2023-08-01 23:59:59-07)') to ('[2023-11-01 00:00:00-07,2023-11-01 23:59:59-07)');

But below query is searching all partitions instead of only the partition which is suppose to contain this data

  select id, time_range  from schedule_slots where  time_range <@ tstzrange('2022-12-01 00:00:00-07','2023-02-01 23:59:59-07', '[]')  ;

I have tried

SET enable_partition_pruning = on;

I have updated the db parameter  constraint_exclusion to on

but still no luck. Please let me know what am I missing. Thanks in advance

CodePudding user response：

Partitioning and partition pruning don't support GiST operator classes. The documentation says:

Range and list partitioning require a btree operator class, while hash partitioning requires a hash operator class. If no operator class is specified explicitly, the default operator class of the appropriate type will be used; if no default operator class exists, an error will be raised.

So there is no chance to get partition pruning in the way you want.

I think that your partitioning strategy is questionable. If your reason for partitioning is to achieve partition pruning, you have a problem. The only way you might get somewhat effective partition pruning is with an additional condition:

select id, time_range
from schedule_slots
where time_range <@ tstzrange('2022-12-01 00:00:00-07','2023-02-01 23:59:59-07', '[]')
  and time_range > tstzrange('2022-12-01 00:00:00-07','2023-02-01 23:59:59-07', '[]');

The additional condition should not change the query result, but allow partition pruning. In this example, that will not be effective.