Re: [HACKERS] Runtime Partition Pruning

Hello,

On Thu, Dec 7, 2017 at 12:52 PM, Beena Emerson <memissemerson(at)gmail(dot)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