scans on table fail to be excluded by partition bounds

From: Justin Pryzby <pryzby(at)telsasoft(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: scans on table fail to be excluded by partition bounds
Date: 2019-06-24 17:31:46
Message-ID: 20190624173146.GA32632@telsasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Steven Winfield 2019-06-25 10:48:01 RE: scans on table fail to be excluded by partition bounds
Previous Message AminPG Jaffer 2019-06-23 15:07:56 Re: Incorrect index used in few cases..