Re: [HACKERS] Runtime Partition Pruning

On Tue, Nov 14, 2017 at 6:27 PM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 14 November 2017 at 19:16, Beena Emerson <memissemerson(at)gmail(dot)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 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=4 width=8)
-> Seq Scan on ab_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