why partition pruning doesn't work?

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема why partition pruning doesn't work?
Дата
Msg-id CAFj8pRBjrufA3ocDm8o4LPGNye9Y+pm1b9kCwode4X04CULG3g@mail.gmail.com
обсуждение исходный текст
Ответы Re: why partition pruning doesn't work?  (Dmitry Dolgov <9erthalion6@gmail.com>)
Список pgsql-hackers
Hi

CREATE TABLE data(a text, vlozeno date) PARTITION BY RANGE(vlozeno);
CREATE TABLE data_2016 PARTITION OF data FOR VALUES FROM ('2016-01-01') TO ('2016-12-31');
CREATE TABLE data_2017 PARTITION OF data FOR VALUES FROM ('2017-01-01') TO ('2017-12-31');
CREATE TABLE data_other PARTITION OF DATA DEFAULT;

insert into data select 'ahoj', '2016-01-01'::date + (random() * 900)::int from generate_series(1,1000000);
analyze data;

postgres=# explain analyze select * from data where vlozeno > '2018-06-01';
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Append (cost=0.00..3519.83 rows=20001 width=9) (actual time=0.042..27.750 rows=19428 loops=1) │
│ -> Seq Scan on data_other (cost=0.00..3419.83 rows=20001 width=9) (actual time=0.040..25.895 rows=19428 loops=1) │
│ Filter: (vlozeno > '2018-06-01'::date) │
│ Rows Removed by Filter: 171518 │
│ Planning Time: 0.766 ms │
│ Execution Time: 28.718 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)

postgres=# explain analyze select * from data where vlozeno > current_date;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Gather (cost=1000.00..17281.36 rows=20080 width=9) (actual time=0.749..95.389 rows=19428 loops=1) │
│ Workers Planned: 2 │
│ Workers Launched: 2 │
│ -> Parallel Append (cost=0.00..14273.36 rows=8367 width=9) (actual time=59.141..89.458 rows=6476 loops=3) │
│ -> Parallel Seq Scan on data_2016 (cost=0.00..5768.69 rows=24 width=9) (actual time=34.847..34.847 rows=0 loops=3) │
│ Filter: (vlozeno > CURRENT_DATE) │
│ Rows Removed by Filter: 135119 │
│ -> Parallel Seq Scan on data_2017 (cost=0.00..5745.02 rows=23 width=9) (actual time=53.269..53.269 rows=0 loops=2) │
│ Filter: (vlozeno > CURRENT_DATE) │
│ Rows Removed by Filter: 201848 │
│ -> Parallel Seq Scan on data_other (cost=0.00..2717.82 rows=11765 width=9) (actual time=0.044..55.502 rows=19428 loops=1) │
│ Filter: (vlozeno > CURRENT_DATE) │
│ Rows Removed by Filter: 171518 │
│ Planning Time: 0.677 ms │
│ Execution Time: 98.349 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(15 rows)

but

postgres=# explain analyze select * from data where vlozeno > (select current_date);
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                      QUERY PLAN                                                      │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Append  (cost=0.01..19574.68 rows=333333 width=9) (actual time=0.095..31.945 rows=19428 loops=1)                     │
│   InitPlan 1 (returns $0)                                                                                            │
│     ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)                           │
│   ->  Seq Scan on data_2016  (cost=0.00..7258.98 rows=135119 width=9) (never executed)                               │
│         Filter: (vlozeno > $0)                                                                                       │
│   ->  Seq Scan on data_2017  (cost=0.00..7229.20 rows=134565 width=9) (never executed)                               │
│         Filter: (vlozeno > $0)                                                                                       │
│   ->  Seq Scan on data_other  (cost=0.00..3419.83 rows=63649 width=9) (actual time=0.069..29.856 rows=19428 loops=1) │
│         Filter: (vlozeno > $0)                                                                                       │
│         Rows Removed by Filter: 171518                                                                               │
│ Planning Time: 0.418 ms                                                                                              │
│ Execution Time: 33.019 ms                                                                                            │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(12 rows)


Partition pruning is working now.

Is it expected? Tested on fresh master.


Regards

Pavel





В списке pgsql-hackers по дате отправления:

Предыдущее
От: Pavel Stehule
Дата:
Сообщение: ToDo: show size of partitioned table
Следующее
От: "REIX, Tony"
Дата:
Сообщение: RE:PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode