I have partitioned a large table in my PG database (6.7 billion rows!) by a date column and in general constraint exclusion works well but only in relatively simple case when the partition key is specified exactly as created in the CHECK constraint. I'm curious if there is a way to get it to work a little more generally though.
For example my CHECK constraint (see code below) specifying a hard-coded field value works well (#1 and #2). Specifying a function that returns a value even though it is the appropriate type scans all of the partitions (#3) unfortunately. Likewise any join, CTE, or sub-query expression, even for a single row that returns the correct type also results in a scan of all of the partitions.
I was curious if there was a way specifically to get #3 to work as the WHERE predicate in this case is stored as an integer but the table itself is partitioned by the appropriate date type. I believe I could work around this issue with dynamic sql in a function but there are lots of cases of this type of simple conversion and I wanted to avoid the maintenance of creating a function per query.
It's also slightly surprising that queries that join with the appropriate type (#4 & #5) also cause a full partition scan. Is there a work-around to get constraint_exclusion to work in this case?
</snip>
-- constraint exclusion tests
-- generate some data
create schema if not exists ptest;
set search_path=ptest;
drop table if exists ptest.tbl cascade;
create table if not exists tbl as select * from (
with a as (
select
generate_series('2014-01-01'::date, now(), '1 day'::interval)::date dt
),
b as (
select
generate_series(1, 1000) i
)
select
a.dt,
b.i,
md5((random()*4+5)::text) str
from
a cross join b
) c;
-- create child partitions
create table ptest.tbl_p2014(check (dt >= '2014-01-01'::date and dt < '2015-01-01'::date)) inherits (ptest.tbl);
create table ptest.tbl_p2015(check (dt >= '2015-01-01'::date and dt < '2016-01-01'::date)) inherits (ptest.tbl);
-- populate child partitions
with pd as ( delete from only ptest.tbl where dt >= '2014-01-01'::date and dt < '2015-01-01'::date returning *)
insert into ptest.tbl_p2014 select * from pd;
with pd as ( delete from only ptest.tbl where dt >= '2015-01-01'::date and dt < '2016-01-01'::date returning *)
insert into ptest.tbl_p2015 select * from pd;
-- clean parent of any data
truncate table only ptest.tbl;
-- create dt field indexes
create index i_tbl_dt on ptest.tbl(dt);
create index i_tbl_dt_p2014 on ptest.tbl_p2014(dt);
create index i_tble_dt_p2015 on ptest.tbl_p2015(dt);
-- vacuum
vacuum analyze verbose ptest.tbl;
-- verify parent is empty and partitions have some data (estimated)
select relname, n_live_tup from pg_stat_user_tables where relname like 'tbl%' and schemaname = 'ptest' order by relname;
-- check that partitions show in parent
\d+ ptest.tbl
-- force constraint_exclusion to partition
set constraint_exclusion = partition;
-- #1: works
explain analyze select count(1) from ptest.tbl where dt = '2014-06-01'::date;
-- #2: works
explain analyze select count(1) from ptest.tbl where dt = DATE '2014-06-01';
-- #3: full scan (no constraint exclusion)
explain analyze select count(1) from ptest.tbl where dt = to_date(201406::text||01::text, 'YYYYMMDD');
-- #4: full scan (no constraint exclusion)
explain analyze select count(1) from ptest.tbl where dt = (select '2014-06-01'::date);
-- #5: full scan (no constraint exclusion)
explain analyze with foo as (select '2014-06-01'::date dt)
select count(1) from ptest.tbl inner join foo on (ptest.tbl.dt = foo.dt);
</snip>
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
