Re: scans on table fail to be excluded by partition bounds

On Tue, 25 Jun 2019 at 05:31, Justin Pryzby <[email protected]> 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