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

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
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=1 width=8)  ->  Seq Scan
onab_a1_b2  (cost=0.00..43.90 rows=1 width=8)
 
<--------- wrong partition        Filter: ((a = $1) AND (b = $2))
(3 rows)

As soon as we hit the generic plan the wrong partition is selected

I think to do this you're going to have to store some sort of array
that maps the partition index to the subpath in the Append node so you
can correctly identify the subpath based on what you're getting back
from get_partitions_for_keys(). Perhaps what you had worked previously
when we were not returning a Bitmapset with that function.

Once you've got that design worked out I can take another look at this.

-- David Rowley                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services