Hi, The mail was accidently sent before I could complete.
On Tue, Jan 9, 2018 at 2:24 PM, Beena Emerson <memissemer...@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