scans on table fail to be excluded by partition bounds

Justin Pryzby <[email protected]>
Newsgroups gmane.comp.db.postgresql.performance
Message-ID <[email protected]>
I came across a poorly performing report with a subplan like this:

ts=# explain SELECT * FROM eric_enodeb_cell_metrics WHERE start_time BETWEEN '2019-01-01 04:00' AND '2019-01-01 05:00' OR start_time BETWEEN '2019-01-02 04:00' AND '2019-01-02 05:00';
 Append  (cost=36.04..39668.56 rows=12817 width=2730)
   ->  Bitmap Heap Scan on eric_enodeb_cell_20190101  (cost=36.04..19504.14 rows=6398 width=2730)
         Recheck Cond: (((start_time >= '2019-01-01 04:00:00-05'::timestamp with time zone) AND (start_time <= '2019-01-01 05:00:00-05'::timestamp with time zone)) OR ((start_time >= '2019-01-02 04:00:00-05'::timestamp with time zone) AND (start_time <= '2019-01-02 05:00:00-05'::timestamp with time zone)))
         ->  BitmapOr  (cost=36.04..36.04 rows=6723 width=0)
               ->  Bitmap Index Scan on eric_enodeb_cell_20190101_idx  (cost=0.00..16.81 rows=6465 width=0)
                     Index Cond: ((start_time >= '2019-01-01 04:00:00-05'::timestamp with time zone) AND (start_time <= '2019-01-01 05:00:00-05'::timestamp with time zone))
               ->  Bitmap Index Scan on eric_enodeb_cell_20190101_idx  (cost=0.00..16.03 rows=259 width=0)
                     Index Cond: ((start_time >= '2019-01-02 04:00:00-05'::timestamp with time zone) AND (start_time <= '2019-01-02 05:00:00-05'::timestamp with time zone))
   ->  Bitmap Heap Scan on eric_enodeb_cell_20190102  (cost=36.08..20100.34 rows=6419 width=2730)
         Recheck Cond: (((start_time >= '2019-01-01 04:00:00-05'::timestamp with time zone) AND (start_time <= '2019-01-01 05:00:00-05'::timestamp with time zone)) OR ((start_time >= '2019-01-02 04:00:00-05'::timestamp with time zone) AND (start_time <= '2019-01-02 05:00:00-05'::timestamp with time zone)))
         ->  BitmapOr  (cost=36.08..36.08 rows=6982 width=0)
               ->  Bitmap Index Scan on eric_enodeb_cell_20190102_idx  (cost=0.00..16.03 rows=259 width=0)
                     Index Cond: ((start_time >= '2019-01-01 04:00:00-05'::timestamp with time zone) AND (start_time <= '2019-01-01 05:00:00-05'::timestamp with time zone))
               ->  Bitmap Index Scan on eric_enodeb_cell_20190102_idx  (cost=0.00..16.84 rows=6723 width=0)
                     Index Cond: ((start_time >= '2019-01-02 04:00:00-05'::timestamp with time zone) AND (start_time <= '2019-01-02 05:00:00-05'::timestamp with time zone))

Is there some reason why the partition constraints aren't excluding any of the
index scans ?  In the actual problem case, there's a longer list of "OR"
conditions and it's even worse.

The partitions looks like this:

Partition of: eric_enodeb_cell_metrics FOR VALUES FROM ('2019-01-02 00:00:00-05') TO ('2019-01-03 00:00:00-05')
Indexes:
    "eric_enodeb_cell_20190102_idx" brin (start_time) WITH (autosummarize='true'), tablespace "oldindex"
    "eric_enodeb_cell_20190102_site_idx" btree (site_id) WITH (fillfactor='100'), tablespace "oldindex"
Check constraints:
    "eric_enodeb_cell_20190102_start_time_check" CHECK (start_time >= '2019-01-02 00:00:00-05'::timestamp with time zone AND start_time < '2019-01-03 00:00:00-05'::timestamp with time zone)
Tablespace: "zfs"

And:
pg_get_partition_constraintdef | ((start_time IS NOT NULL) AND (start_time >= '2019-01-02 00:00:00-05'::timestamp with time zone) AND (start_time < '2019-01-03 00:00:00-05'::timestamp with time zone))

ts=# SELECT version();
version | PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit

ts=# SHOW constraint_exclusion ;
constraint_exclusion | partition

ts=# SHOW enable_partition_pruning;
enable_partition_pruning | on

Thanks in advance.

Justin
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.