Partition Constraint Exclusion Limits - Mailing list pgsql-performance

I have partitioned a large table in my PG database (6.7 billion rows!) by a date column and in general constraint
exclusionworks well but only in relatively simple case when the partition key is specified exactly as created in the
CHECKconstraint.  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).
Specifyinga 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
alsoresults 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
integerbut the table itself is partitioned by the appropriate date type.  I believe I could work around this issue with
dynamicsql in a function but there are lots of cases of this type of simple conversion and I wanted to avoid the
maintenanceof 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>