Re: [HACKERS] Runtime Partition Pruning

Hello,

On Sun, Jan 7, 2018 at 5:31 PM, David Rowley
<david.rowley@2ndquadrant.com> wrote:
> On 7 January 2018 at 00:03, David Rowley <david.rowley@2ndquadrant.com> wrote:
>> I've fixed this in the attached, but I did so by calling
>> adjust_appendrel_attrs() from the nodeAppend.c, which did, of course,
>> mean that the AppendRelInfo needed to be given to the executor. I was
>> also a bit unsure what exactly I should be doing in primnodes.h, since
>> I've put PartitionPruneInfo in there, but AppendRelInfo is not. I
>> stuck a quick declaration of AppendRelInfo in primnode.h with an XXX
>> comment so we don't forget to think about that again.
>
> Actually, this was not a very smart fix for the problem. It seems much
> better to make the prune qual part of PartitionPruneInfo and just have
> the planner translate the qual to what's required for the partition
> that the PartitionPruneInfo belongs to. This means we no longer need
> to use the Append's qual to store the prune qual and that all the
> pruning information for one partition is now neatly in a single
> struct.
>
> I've attached a patch which does things like this.


The pruning does not work well with char type:

Case: A subpartition has a different col order and the subpartitioned
col is type char.

drop table ab_c;
 create table ab_c (a int not null, b char) partition by list(a);
  create table abc_a2 (b char, 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);
  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;

INSERT INTO ab_c VALUES (1,'1'), (1,'2'), (1,'3');
INSERT INTO ab_c VALUES (2,'1'), (2,'2'), (2,'3');
INSERT INTO ab_c VALUES (3,'1'), (3,'2'), (3,'3');

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

--after 5 runs: abc_a2_b3 is not pruned.

# explain (analyze, costs off, summary off, timing off) execute abc_q1
(1, 2, '2');
                       QUERY PLAN
---------------------------------------------------------
 Append (actual rows=4 loops=1)
   ->  Seq Scan on abc_a1_b1 (actual rows=1 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
   ->  Seq Scan on abc_a1_b2 (actual rows=1 loops=1)
         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=1 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
   ->  Seq Scan on abc_a2_b2 (actual rows=1 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))
         Rows Removed by Filter: 1
   ->  Seq Scan on abc_a3_b1 (never executed)
         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))
(20 rows)


Case 2: Case with optimizer pruning
drop table ab_c;
 create table ab_c (a int not null, b int) partition by list(a);
 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);
  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;


=# explain (analyze, costs off, summary off, timing off) execute abc_q1 (1, 1);
                                  QUERY PLAN
------------------------------------------------------------------------------
 Append (actual rows=2 loops=1)
   ->  Seq Scan on abc_a1_b2 (actual rows=1 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b = ANY ('{3,2}'::integer[])))
   ->  Seq Scan on abc_a1_b3 (actual rows=1 loops=1)
         Filter: ((a >= $1) AND (a <= $2) AND (b = ANY ('{3,2}'::integer[])))
   ->  Seq Scan on abc_a2_b2 (never executed)
         Filter: ((a >= $1) AND (a <= $2) AND (b = ANY ('{3,2}'::integer[])))
   ->  Seq Scan on abc_a2_b3 (never executed)
         Filter: ((a >= $1) AND (a <= $2) AND (b = ANY ('{3,2}'::integer[])))
   ->  Seq Scan on abc_a3_b2 (never executed)
         Filter: ((a >= $1) AND (a <= $2) AND (b = ANY ('{3,2}'::integer[])))
   ->  Seq Scan on abc_a3_b3 (never executed)
         Filter: ((a >= $1) AND (a <= $2) AND (b = ANY ('{3,2}'::integer[])))
(13 rows)

postgres=# explain (analyze, costs off, summary off, timing off)
execute abc_q1 (1, 2);
ERROR:  partition missing from Append subplans

-- 

Beena Emerson

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