From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | why partition pruning doesn't work? |
Date: | 2018-06-01 05:19:01 |
Message-ID: | CAFj8pRBjrufA3ocDm8o4LPGNye9Y+pm1b9kCwode4X04CULG3g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
The commit message
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=499be013de65242235ebdde06adb08db887f0ea5
says so append should be supported.
Regards
Pavel
From | Date | Subject | |
---|---|---|---|
Next Message | REIX, Tony | 2018-06-01 08:13:38 | RE:PostgreSQL 11 beta1 on AIX 7.2 : 2 failures in 32bit mode |
Previous Message | Pavel Stehule | 2018-06-01 04:56:22 | ToDo: show size of partitioned table |