Обсуждение: scans on table fail to be excluded by partition bounds
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' ORstart_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-0105: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-0105: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-0205: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-0105: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-0105: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-0205: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 zoneAND 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 timezone) 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
> 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' Maybe it's because of the implicit usage of the local timezone when the strings are cast to (timestamp with time zone) inthe values you give for start_time here? What happens if you specify it using "TIMESTAMP WITH TIME ZONE '2019-01-01 04:00-05'", etc.? Steve.
On Tue, Jun 25, 2019 at 10:48:01AM +0000, Steven Winfield wrote: > > 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' > > Maybe it's because of the implicit usage of the local timezone when the strings are cast to (timestamp with time zone)in the values you give for start_time here? > What happens if you specify it using "TIMESTAMP WITH TIME ZONE '2019-01-01 04:00-05'", etc.? It's the same. The timezone in the constraints is the default timezone so the that's correct. Justin
On Tue, 25 Jun 2019 at 05:31, Justin Pryzby <pryzby@telsasoft.com> wrote: > 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-0105: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-0105: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 ? Yeah, we don't do anything to remove base quals that are redundant due to the partition constraint. There was a patch [1] to try and fix this but it's not seen any recent activity. [1] https://commitfest.postgresql.org/19/1264/ -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services