Re: [HACKERS] Runtime Partition Pruning

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