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.
