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

On Tue, Nov 14, 2017 at 6:27 PM, David Rowley
<david.rowley@2ndquadrant.com> wrote:
> On 14 November 2017 at 19:16, Beena Emerson <memissemerson@gmail.com> wrote:
>> PFA the updated patches.
>
> Hi Beena,
>
> Thanks for working on this. I've had a look at the patch to try to
> understand how it is working. I found it a bit surprising that the
> code assumes it can rely on the order of Append->appendplans matching
> what's needed based on the return value of get_partitions_for_keys().
>
> I tried using the following to break this:
>
>
> drop table if exists ab;
> create table ab (a int not null, b int not null) partition by list(a);
> create table ab_a2 partition of ab for values in(2) partition by list (b);
> create table ab_a2_b1 partition of ab_a2 for values in (1);
> create table ab_a2_b2 partition of ab_a2 for values in (2);
> create table ab_a2_b3 partition of ab_a2 for values in (3);
>
> create table ab_a1 partition of ab for values in(1) partition by list (b);
> create table ab_a1_b1 partition of ab_a1 for values in (1);
> create table ab_a1_b2 partition of ab_a1 for values in (2);
> create table ab_a1_b3 partition of ab_a1 for values in (3);
> create table ab_a3 partition of ab for values in(3) partition by list (b);
> create table ab_a3_b1 partition of ab_a3 for values in (1);
> create table ab_a3_b2 partition of ab_a3 for values in (2);
> create table ab_a3_b3 partition of ab_a3 for values in (3);
>
> prepare ab_q1 (int, int) as select * from ab where a = $1 and b = $2;
>
> explain execute ab_q1 (2,3);
> explain execute ab_q1 (2,3);
> explain execute ab_q1 (2,3);
> explain execute ab_q1 (2,3);
>
> postgres=# explain execute ab_q1 (2,3);
>                           QUERY PLAN
> ---------------------------------------------------------------
>  Append  (cost=0.00..43.90 rows=1 width=8)
>    ->  Seq Scan on ab_a2_b3  (cost=0.00..43.90 rows=1 width=8)
>          Filter: ((a = 2) AND (b = 3))
> (3 rows)
>
>
> postgres=# explain execute ab_q1 (2,3);
>                           QUERY PLAN
> ---------------------------------------------------------------
>  Append  (cost=0.00..395.10 rows=1 width=8)
>    ->  Seq Scan on ab_a1_b2  (cost=0.00..43.90 rows=1 width=8)
> <--------- wrong partition
>          Filter: ((a = $1) AND (b = $2))
> (3 rows)
>

With the new patch, the output is:
postgres=#  explain execute ab_q1 (2,3);                         QUERY PLAN
---------------------------------------------------------------Append  (cost=0.00..43.90 rows=1 width=8)  ->  Seq Scan
onab_a2_b3  (cost=0.00..43.90 rows=1 width=8)        Filter: ((a = 2) AND (b = 3))
 
(3 rows)

postgres=#  explain execute ab_q1 (2,3);                         QUERY PLAN
---------------------------------------------------------------Append  (cost=0.00..395.10 rows=4 width=8)  ->  Seq Scan
onab_a2_b3  (cost=0.00..43.90 rows=1 width=8)        Filter: ((a = $1) AND (b = $2))
 
(3 rows)


-- 

Beena Emerson

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