Re: [HACKERS] Runtime Partition Pruning

Hello,

On Fri, Jan 5, 2018 at 6:24 AM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 5 January 2018 at 05:37, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>> I tried this patch (applying it on Amit's last current version on top of
>> 4e2970f8807f which is the latest it applies to) and regression tests
>> fail with the attached diff; in all cases it appears to be an off-by-one
>> in row count. Would you please give it a look?
>
> Thanks for testing. I've attached an updated patch which hopefully fixes this.
>
> I've only thing I did to fix it was to alter the tests a bit so that
> the row counts in explain are evenly divisible by the nloops or
> parallel workers. Looks like it was failing due to platform dependent
> behaviour in printf.
>

It does not handle change in column order (varattno) in subpartitions.

In the following case a2 has different column order
drop table ab_c;
create table ab_c (a int not null, b int) partition by list(a);

--a2 with different col order
create table abc_a2 (b int, a int not null) partition by list(b);
create table abc_a2_b1 partition of abc_a2 for values in (1);
create table abc_a2_b2 partition of abc_a2 for values in (2);
create table abc_a2_b3 partition of abc_a2 for values in (3);
alter table ab_c attach partition abc_a2 for values in (2);

--a1 and a3 with same col order as the parent
create table abc_a1 partition of ab_c for values in(1) partition by list (b);
create table abc_a1_b1 partition of abc_a1 for values in (1);
create table abc_a1_b2 partition of abc_a1 for values in (2);
create table abc_a1_b3 partition of abc_a1 for values in (3);
create table abc_a3 partition of ab_c for values in(3) partition by list (b);
create table abc_a3_b1 partition of abc_a3 for values in (1);
create table abc_a3_b2 partition of abc_a3 for values in (2);
create table abc_a3_b3 partition of abc_a3 for values in (3);

deallocate abc_q1;
prepare abc_q1 (int, int, int) as select * from ab_c where a BETWEEN
$1 and $2 AND b <= $3;

--optimizer pruning
explain (analyze, costs off, summary off, timing off) execute abc_q1 (1, 3, 1);
QUERY PLAN
------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on abc_a1_b1 (actual rows=0 loops=1)
Filter: ((a >= 1) AND (a <= 3) AND (b <= 1))
-> Seq Scan on abc_a2_b1 (actual rows=0 loops=1)
Filter: ((a >= 1) AND (a <= 3) AND (b <= 1))
-> Seq Scan on abc_a3_b1 (actual rows=0 loops=1)
Filter: ((a >= 1) AND (a <= 3) AND (b <= 1))
(7 rows)

--runtime pruning after 5 runs
explain (analyze, costs off, summary off, timing off) execute abc_q1 (1, 3, 1);
QUERY PLAN
---------------------------------------------------------
Append (actual rows=0 loops=1)
-> Seq Scan on abc_a1_b1 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on abc_a1_b2 (never executed)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on abc_a1_b3 (never executed)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on abc_a2_b1 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on abc_a2_b2 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on abc_a2_b3 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on abc_a3_b1 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on abc_a3_b2 (never executed)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on abc_a3_b3 (never executed)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
(19 rows)

As seen partition a2 does not prune like in other 2 subpartitions - a1 and a3.
--

Beena Emerson

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