Re: [HACKERS] Runtime Partition Pruning

Hello Robert,

On Sat, Dec 2, 2017 at 12:34 AM, 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.
>

The output above is shown for a Const Value i.e. optimizer pruning
which I included just to show that the correct partition is chosen
even during runtime pruning for the given value. So taking your
suggestion, the output for runtime pruning could be something as
follows:

postgres=#  explain analyse execute ab_q1 (3,3);
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..395.10 rows=9 width=8) (actual time=0.119..0.119
rows=0 loops=1) (run-time partition pruning: on)
   ->  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)


Beena Emerson

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