pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedscans on table fail to be excluded by partition bounds
4+ messages / 3 participants
[nested] [flat]
* scans on table fail to be excluded by partition bounds
@ 2019-06-24 17:31 Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 2 replies; 4+ messages in thread
From: Justin Pryzby @ 2019-06-24 17:31 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: scans on table fail to be excluded by partition bounds
@ 2019-06-25 10:48 Steven Winfield <Steven.Winfield@cantabcapital.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 1 reply; 4+ messages in thread
From: Steven Winfield @ 2019-06-25 10:48 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance
> 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.?
Steve.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: scans on table fail to be excluded by partition bounds
@ 2019-06-26 18:03 Justin Pryzby <pryzby@telsasoft.com>
parent: Steven Winfield <Steven.Winfield@cantabcapital.com>
0 siblings, 0 replies; 4+ messages in thread
From: Justin Pryzby @ 2019-06-26 18:03 UTC (permalink / raw)
To: Steven Winfield <Steven.Winfield@cantabcapital.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: scans on table fail to be excluded by partition bounds
@ 2019-06-26 22:09 David Rowley <david.rowley@2ndquadrant.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 4+ messages in thread
From: David Rowley @ 2019-06-26 22:09 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance
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-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 ?
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-06-26 22:09 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-06-24 17:31 scans on table fail to be excluded by partition bounds Justin Pryzby <pryzby@telsasoft.com>
2019-06-25 10:48 ` Steven Winfield <Steven.Winfield@cantabcapital.com>
2019-06-26 18:03 ` Justin Pryzby <pryzby@telsasoft.com>
2019-06-26 22:09 ` David Rowley <david.rowley@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox