Re: [HACKERS] Runtime Partition Pruning

Hello,

On Fri, Jan 5, 2018 at 6:24 AM, David Rowley
<david.rowley@2ndquadrant.com> wrote:
> On 5 January 2018 at 05:37, Alvaro Herrera <alvherre@alvh.no-ip.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