Hello, On Thu, Dec 7, 2017 at 12:52 PM, Beena Emerson <memissemer...@gmail.com> wrote: > > 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) >
FYI, The v4 version of the patch accidentally included the choose_custom_plan hack I had used to force the runtime pruning in the above cases(1,2), which has been removed in v5. So with only the patch applied, it would continue to give the output as with the const and not the Param because the custom plan is preferred over the generic one. This was pointed out in the initial post of this thread. Just to compare, I continued using the hack for the tests to show the behaviour changes. A different case would need to be used to test the behaviour which picks the generic plan. -- Beena Emerson EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
