Re: [HACKERS] Runtime Partition Pruning

On Wed, Dec 6, 2017 at 1:21 PM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 2 December 2017 at 08:04, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Fri, Dec 1, 2017 at 6:20 AM, Beena Emerson <memissemerson(at)gmail(dot)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