Range Partititioning & Constraint Exclusion Oddities - Mailing list pgsql-general

Hi, appreciate if someone can help shed some light on what i may be doing wrong.

I know there are caveat on using constraint exclusion to reduce the # of partitions scanned.

pg:8.2.9

create table test (
code varchar,
dummy_col1 int,
dummy_col2 int
)

create table test_experimental_code (
code varchar,
dummy_col1 int,
dummy_col2 int
) inherits(test)


alter table test_experimental_code add check (code not in ('P000','000','0'))
alter table test_prod_code add check (code in ('P000','000','0'))

insert into test_prod_code(code, dummy_col1, dummy_col2) values ('P000',1,1),('000',2,2),('0',3,3),('P000',44,44)
insert into test_experimental_code(code, dummy_col1, dummy_col2) values
('AAA',1,1),('BBB',2,2),('BBC',3,3),('DAD',44,44)

set constraint_exclusion = on
select count(*) from test [Expected]
"Aggregate  (cost=71.25..71.26 rows=1 width=0)"
"  ->  Append  (cost=0.00..63.00 rows=3300 width=0)"
"        ->  Seq Scan on test  (cost=0.00..21.00 rows=1100 width=0)"
"        ->  Seq Scan on test_prod_code test  (cost=0.00..21.00 rows=1100 width=0)"
"        ->  Seq Scan on test_experimental_code test  (cost=0.00..21.00 rows=1100 width=0)"

select count(*) from test where code = 'AAA' <--[NOT expected result]
"Aggregate  (cost=71.30..71.31 rows=1 width=0)"
"  ->  Append  (cost=0.00..71.25 rows=18 width=0)"
"        ->  Seq Scan on test  (cost=0.00..23.75 rows=6 width=0)"
"              Filter: ((code)::text = 'AAA'::text)"
"        ->  Seq Scan on test_prod_code test  (cost=0.00..23.75 rows=6 width=0)"
"              Filter: ((code)::text = 'AAA'::text)"
"        ->  Seq Scan on test_experimental_code test  (cost=0.00..23.75 rows=6 width=0)"
"              Filter: ((code)::text = 'AAA'::text)"

select count(*) from test where code = 'AAA' and code not in ('P000','000','0') <--[I thought this would help]
"Aggregate  (cost=91.92..91.92 rows=1 width=0)"
"  ->  Append  (cost=0.00..91.88 rows=15 width=0)"
"        ->  Seq Scan on test  (cost=0.00..30.62 rows=5 width=0)"
"              Filter: (((code)::text = 'AAA'::text) AND ((code)::text <> ALL (('{P000,000,0}'::character
varying[])::text[])))"
"        ->  Seq Scan on test_prod_code test  (cost=0.00..30.62 rows=5 width=0)"
"              Filter: (((code)::text = 'AAA'::text) AND ((code)::text <> ALL (('{P000,000,0}'::character
varying[])::text[])))"
"        ->  Seq Scan on test_experimental_code test  (cost=0.00..30.62 rows=5 width=0)"
"              Filter: (((code)::text = 'AAA'::text) AND ((code)::text <> ALL (('{P000,000,0}'::character
varying[])::text[])))"

select count(*) from test where code in ('P000','000','0') <--[NOT Expected result]
"Aggregate  (cost=83.75..83.76 rows=1 width=0)"
"  ->  Append  (cost=0.00..83.62 rows=48 width=0)"
"        ->  Seq Scan on test  (cost=0.00..27.88 rows=16 width=0)"
"              Filter: ((code)::text = ANY (('{P000,000,0}'::character varying[])::text[]))"
"        ->  Seq Scan on test_prod_code test  (cost=0.00..27.88 rows=16 width=0)"
"              Filter: ((code)::text = ANY (('{P000,000,0}'::character varying[])::text[]))"
"        ->  Seq Scan on test_experimental_code test  (cost=0.00..27.88 rows=16 width=0)"
"              Filter: ((code)::text = ANY (('{P000,000,0}'::character varying[])::text[]))"

ALTER TABLE test_prod_code DROP CONSTRAINT test_prod_code_code_check;
ALTER TABLE test_experimental_code DROP CONSTRAINT test_experimental_code_code_check;
alter table test_prod_code add check (code = 'PROD')
alter table test_experimental_code add check (code <> 'PROD')
update test_prod_code set code ='PROD'

select count(*) from test where code = 'AAA' <<-- Expected Result
"Aggregate  (cost=47.53..47.54 rows=1 width=0)"
"  ->  Append  (cost=0.00..47.50 rows=12 width=0)"
"        ->  Seq Scan on test  (cost=0.00..23.75 rows=6 width=0)"
"              Filter: ((code)::text = 'AAA'::text)"
"        ->  Seq Scan on test_experimental_code test  (cost=0.00..23.75 rows=6 width=0)"
"              Filter: ((code)::text = 'AAA'::text)"


select count(*) from test where code::text in ('AAA'::character varying,'BBB'::character varying) <<-- Explicit
data-type 
"Aggregate  (cost=47.56..47.57 rows=1 width=0)"
"  ->  Append  (cost=0.00..47.50 rows=22 width=0)"
"        ->  Seq Scan on test  (cost=0.00..23.75 rows=11 width=0)"
"              Filter: ((code)::text = ANY ('{AAA,BBB}'::text[]))"
"        ->  Seq Scan on test_experimental_code test  (cost=0.00..23.75 rows=11 width=0)"
"              Filter: ((code)::text = ANY ('{AAA,BBB}'::text[]))"


select count(*) from test where code in ('AAA','BBB') <-- W/o it it will query all partitions
"Aggregate  (cost=79.58..79.59 rows=1 width=0)"
"  ->  Append  (cost=0.00..79.50 rows=33 width=0)"
"        ->  Seq Scan on test  (cost=0.00..26.50 rows=11 width=0)"
"              Filter: ((code)::text = ANY (('{AAA,BBB}'::character varying[])::text[]))"
"        ->  Seq Scan on test_prod_code test  (cost=0.00..26.50 rows=11 width=0)"
"              Filter: ((code)::text = ANY (('{AAA,BBB}'::character varying[])::text[]))"
"        ->  Seq Scan on test_experimental_code test  (cost=0.00..26.50 rows=11 width=0)"
"              Filter: ((code)::text = ANY (('{AAA,BBB}'::character varying[])::text[]))"


select count(*) from test where code::text in ('AAA','BBB') <<<-- explicit data-type on left hand side
"Aggregate  (cost=47.56..47.57 rows=1 width=0)"
"  ->  Append  (cost=0.00..47.50 rows=22 width=0)"
"        ->  Seq Scan on test  (cost=0.00..23.75 rows=11 width=0)"
"              Filter: ((code)::text = ANY ('{AAA,BBB}'::text[]))"
"        ->  Seq Scan on test_experimental_code test  (cost=0.00..23.75 rows=11 width=0)"
"              Filter: ((code)::text = ANY ('{AAA,BBB}'::text[]))"


select count(*) from test where code in ('AAA','BBB')and code not in ('PROD')
"Aggregate  (cost=58.56..58.57 rows=1 width=0)"
"  ->  Append  (cost=0.00..58.50 rows=22 width=0)"
"        ->  Seq Scan on test  (cost=0.00..29.25 rows=11 width=0)"
"              Filter: (((code)::text = ANY (('{AAA,BBB}'::character varying[])::text[])) AND ((code)::text <>
'PROD'::text))"
"        ->  Seq Scan on test_experimental_code test  (cost=0.00..29.25 rows=11 width=0)"
"              Filter: (((code)::text = ANY (('{AAA,BBB}'::character varying[])::text[])) AND ((code)::text <>
'PROD'::text))"


select count(*) from test where code = 'AAA' and code not in ('PROD')
"Aggregate  (cost=53.03..53.04 rows=1 width=0)"
"  ->  Append  (cost=0.00..53.00 rows=10 width=0)"
"        ->  Seq Scan on test  (cost=0.00..26.50 rows=5 width=0)"
"              Filter: (((code)::text = 'AAA'::text) AND ((code)::text <> 'PROD'::text))"
"        ->  Seq Scan on test_experimental_code test  (cost=0.00..26.50 rows=5 width=0)"
"              Filter: (((code)::text = 'AAA'::text) AND ((code)::text <> 'PROD'::text))"

select count(*) from test where code in ('PROD')
"Aggregate  (cost=47.53..47.54 rows=1 width=0)"
"  ->  Append  (cost=0.00..47.50 rows=12 width=0)"
"        ->  Seq Scan on test  (cost=0.00..23.75 rows=6 width=0)"
"              Filter: ((code)::text = 'PROD'::text)"
"        ->  Seq Scan on test_prod_code test  (cost=0.00..23.75 rows=6 width=0)"
"              Filter: ((code)::text = 'PROD'::text)"

select count(*) from test where code = 'PROD'
"Aggregate  (cost=47.53..47.54 rows=1 width=0)"
"  ->  Append  (cost=0.00..47.50 rows=12 width=0)"
"        ->  Seq Scan on test  (cost=0.00..23.75 rows=6 width=0)"
"              Filter: ((code)::text = 'PROD'::text)"
"        ->  Seq Scan on test_prod_code test  (cost=0.00..23.75 rows=6 width=0)"
"              Filter: ((code)::text = 'PROD'::text)"



I believe that perhaps there may be something wrong with how the
data-type is being interpreted. Additionally, if the check conditions
are in a range, then seems like the planner will ignore it?


check condition
-->alter table test_prod_code add check (code = 'PROD')
becomes
-->ALTER TABLE test_prod_code ADD CONSTRAINT test_prod_code_code_check
CHECK (code::text = 'PROD'::text);

I'm considering implemenenting partitioning, however, I need to
understand the caveats beforehand.