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

Hi,

The mail was accidently sent before I could complete.

On Tue, Jan 9, 2018 at 2:24 PM, Beena Emerson <memissemerson@gmail.com> wrote:
> Hello,
>
> The pruning does not work well with char type:
>
> 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;

Prepared statement is missing:
 prepare abc_q1 (int, int) as select a,b from ab_c where a BETWEEN $1
and $2 AND b IN (3, 2);
>
>
> =# 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

These work fine when the column order of subpartitons are not changed.

Case 3: Optimizer pruning with char types:
Same as case1 with all subpartitions having same col order as parent.

drop table ab_c;
 create table ab_c (a int not null, b char) partition by list(a);
 create table abc_a2 ( a int not null, b char) 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;
  prepare abc_q1 (int, int) as select a,b from ab_c where a BETWEEN $1
and $2 AND b IN ('3', '2');

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

-- after 5 runs

=# explain (analyze, costs off, summary off, timing off) execute abc_q1 (1, 1);
ERROR:  operator 1057 is not a member of opfamily 1976



-- 

Beena Emerson

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