On Wed, Dec 6, 2017 at 1:21 PM, David Rowley <david.row...@2ndquadrant.com> wrote: > On 2 December 2017 at 08:04, Robert Haas <robertmh...@gmail.com> wrote: >> On Fri, Dec 1, 2017 at 6:20 AM, Beena Emerson <memissemer...@gmail.com> >> wrote: >>> David Q1: >>> postgres=# explain analyse execute ab_q1 (3,3); --const >>> QUERY PLAN >>> --------------------------------------------------------------------------------------------------------- >>> Append (cost=0.00..43.90 rows=1 width=8) (actual time=0.006..0.006 >>> rows=0 loops=1) >>> -> Seq Scan on ab_a3_b3 (cost=0.00..43.90 rows=1 width=8) (actual >>> time=0.005..0.005 rows=0 loops=1) >>> Filter: ((a = 3) AND (b = 3)) >>> Planning time: 0.588 ms >>> Execution time: 0.043 ms >>> (5 rows) >> >> I think the EXPLAIN ANALYZE input should show something attached to >> the Append node so that we can tell that partition pruning is in use. >> I'm not sure if that is as simple as "Run-Time Partition Pruning: Yes" >> or if we can give a few more useful details. > > It already does. Anything subnode with "(never executed)" was pruned > at runtime. Do we really need anything else to tell us that?
I have added the partition quals that are used for pruning.
PFA the updated patch. I have changed the names of variables to make
it more appropriate, along with adding more code comments and doing
some refactoring and other code cleanups.
Few cases:
1. Only runtime pruning - David's case1
explain analyse execute ab_q1 (2,3);
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Append (cost=0.00..395.10 rows=9 width=8) (actual time=0.101..0.101
rows=0 loops=1)
Runtime Partition Pruning: ((a = $1) AND (b = $2))
-> Seq Scan on ab_a1_b1 (cost=0.00..43.90 rows=1 width=8) (never executed)
Filter: ((a = $1) AND (b = $2))
-> Seq Scan on ab_a1_b2 (cost=0.00..43.90 rows=1 width=8) (never executed)
Filter: ((a = $1) AND (b = $2))
-> Seq Scan on ab_a1_b3 (cost=0.00..43.90 rows=1 width=8) (never executed)
Filter: ((a = $1) AND (b = $2))
-> Seq Scan on ab_a2_b1 (cost=0.00..43.90 rows=1 width=8) (never executed)
Filter: ((a = $1) AND (b = $2))
-> Seq Scan on ab_a2_b2 (cost=0.00..43.90 rows=1 width=8) (never executed)
Filter: ((a = $1) AND (b = $2))
-> Seq Scan on ab_a2_b3 (cost=0.00..43.90 rows=1 width=8) (actual
time=0.007..0.007 rows=0 loops=1)
Filter: ((a = $1) AND (b = $2))
-> Seq Scan on ab_a3_b1 (cost=0.00..43.90 rows=1 width=8) (never executed)
Filter: ((a = $1) AND (b = $2))
-> Seq Scan on ab_a3_b2 (cost=0.00..43.90 rows=1 width=8) (never executed)
Filter: ((a = $1) AND (b = $2))
-> Seq Scan on ab_a3_b3 (cost=0.00..43.90 rows=1 width=8) (never executed)
Filter: ((a = $1) AND (b = $2))
Planning time: 0.780 ms
Execution time: 0.220 ms
(22 rows)
2. Runtime pruning after optimizer pruning - David's case 2.
((a >= 4) AND (a <= 5) is used during optimizer pruning and only (a =
$1) is used for runtime pruning.
=# explain (analyse, costs off, summary off) execute ab_q1 (4);
QUERY PLAN
-------------------------------------------------------------------
Append (actual time=0.062..0.062 rows=0 loops=1)
Runtime Partition Pruning: (a = $1)
-> Seq Scan on ab_a4 (actual time=0.005..0.005 rows=0 loops=1)
Filter: ((a >= 4) AND (a <= 5) AND (a = $1))
-> Seq Scan on ab_a5 (never executed)
Filter: ((a >= 4) AND (a <= 5) AND (a = $1))
(6 rows)
3. Nestloop Join
tbl1.col1 only has values from 1 to 10.
=# \d+ tprt
Table "public.tprt"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
col1 | integer | | | | plain | |
col2 | integer | | | | plain | |
Partition key: RANGE (col1)
Partitions: tprt_1 FOR VALUES FROM (1) TO (5001),
tprt_2 FOR VALUES FROM (5001) TO (10001),
tprt_3 FOR VALUES FROM (10001) TO (20001)
=# explain (analyse, costs off, summary off) SELECT * FROM tbl1 JOIN
tprt ON tbl1.col1 > tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------------------------
Nested Loop (actual time=0.053..0.192 rows=45 loops=1)
-> Seq Scan on tbl1 (actual time=0.007..0.009 rows=10 loops=1)
-> Append (actual time=0.003..0.004 rows=4 loops=10)
Runtime Partition Pruning Join Filter: (tbl1.col1 > col1)
-> Index Scan using tprt1_idx on tprt_1 (actual
time=0.002..0.004 rows=5 loops=9)
Index Cond: (tbl1.col1 > col1)
-> Index Scan using tprt2_idx on tprt_2 (never executed)
Index Cond: (tbl1.col1 > col1)
-> Index Scan using tprt3_idx on tprt_3 (never executed)
Index Cond: (tbl1.col1 > col1)
(10 rows)
4. InitPlan - Raghu's test case:
4.1 Only few partitions satisfy the param
explain (analyse, costs off, summary off) SELECT * FROM prun_test_part
WHERE sal < (SELECT sal FROM prun_test_part WHERE sal = 200);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Append (actual time=0.034..0.038 rows=3 loops=1)
Runtime Partition Pruning: (sal < $0)
InitPlan 1 (returns $0)
-> Append (actual time=0.008..0.009 rows=1 loops=1)
-> Seq Scan on prun_test_part_p3 prun_test_part_p3_1
(actual time=0.008..0.009 rows=1 loops=1)
Filter: (sal = 200)
Rows Removed by Filter: 1
-> Seq Scan on prun_test_part_p1 (actual time=0.002..0.003 rows=1 loops=1)
Filter: (sal < $0)
-> Seq Scan on prun_test_part_p2 (actual time=0.002..0.003 rows=2 loops=1)
Filter: (sal < $0)
-> Seq Scan on prun_test_part_p3 (never executed)
Filter: (sal < $0)
-> Seq Scan on prun_test_part_p4 (never executed)
Filter: (sal < $0)
(15 rows)
4.2 When the InitPlan query returns nothing
=# explain (analyse, costs off, summary off) SELECT * FROM
prun_test_part WHERE sal < (SELECT sal FROM prun_test_part WHERE sal =
50);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Append (actual time=0.050..0.050 rows=0 loops=1)
Runtime Partition Pruning: (sal < $0)
InitPlan 1 (returns $0)
-> Append (actual time=0.013..0.013 rows=0 loops=1)
-> Seq Scan on prun_test_part_p1 prun_test_part_p1_1
(actual time=0.012..0.012 rows=0 loops=1)
Filter: (sal = 50)
Rows Removed by Filter: 1
-> Seq Scan on prun_test_part_p1 (never executed)
Filter: (sal < $0)
-> Seq Scan on prun_test_part_p2 (never executed)
Filter: (sal < $0)
-> Seq Scan on prun_test_part_p3 (never executed)
Filter: (sal < $0)
-> Seq Scan on prun_test_part_p4 (never executed)
Filter: (sal < $0)
(15 rows)
--
Beena Emerson
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
0001-Implement-runtime-partiton-pruning_v5.patch
Description: Binary data
