Re: [HACKERS] Runtime Partition Pruning - Mailing list pgsql-hackers

Hello,

PFA the updated patch rebased over Amit's v13 patches [1] part of
which is committed. This also fixes few bugs I found.
The InitPlans require execPlan which is not set during ExecInitAppend
and so the evaluation of extern quals is moved from ExecInitAppend to
ExecAppend. This changes the output of explain but only the correct
partition(s) are scanned.

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)

postgres=#  explain analyse execute ab_q1 (3,3); --Param only
ab_a3_b3 plan is executed
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..395.10 rows=9 width=8) (actual time=0.119..0.119
rows=0 loops=1)
   ->  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) (never executed)
         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) (actual
time=0.006..0.006 rows=0 loops=1)
         Filter: ((a = $1) AND (b = $2))
 Planning time: 0.828 ms
 Execution time: 0.234 ms
(21 rows)


David Q1
postgres=#  explain analyse execute ab_q1 (4); -- Const
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..49.55 rows=1 width=8) (actual time=0.005..0.005
rows=0 loops=1)
   ->  Seq Scan on ab_a4  (cost=0.00..49.55 rows=1 width=8) (actual
time=0.004..0.004 rows=0 loops=1)
         Filter: ((a >= 4) AND (a <= 5) AND (a = 4))
 Planning time: 0.501 ms
 Execution time: 0.039 ms
(5 rows)

postgres=#  explain analyse execute ab_q1 (4); --Param
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..99.10 rows=2 width=8) (actual time=0.063..0.063
rows=0 loops=1)
   ->  Seq Scan on ab_a4  (cost=0.00..49.55 rows=1 width=8) (actual
time=0.004..0.004 rows=0 loops=1)
         Filter: ((a >= 4) AND (a <= 5) AND (a = $1))
   ->  Seq Scan on ab_a5  (cost=0.00..49.55 rows=1 width=8) (never executed)
         Filter: ((a >= 4) AND (a <= 5) AND (a = $1))
 Planning time: 0.563 ms
 Execution time: 0.111 ms


I am still working on the patch to add more comments and regression
tests but comments on the code is welcome.

[1]https://www.postgresql.org/message-id/df609168-b7fd-4c0b-e9b2-6e398d411e27%40lab.ntt.co.jp

-- 

Beena Emerson

EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company