From: GMail <mfwilson@gmail.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: Partition Constraint Exclusion Limits
Message-Id: <7C9FB5CD-7AF6-4167-8CCA-2075BD9091E2@gmail.com>
Date: Tue, 27 Oct 2015 11:29:04 -0700
To: pgsql-performance@postgresql.org
Mime-Version: 1.0 (Mac OS X Mail 8.2 \(2104\))
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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 rel=
atively 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 fi=
eld value works well (#1 and #2).  Specifying a function that returns a val=
ue 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.=20=20

I was curious if there was a way specifically to get #3 to work as the WHER=
E predicate in this case is stored as an integer but the table itself is pa=
rtitioned 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 t=
ype 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 t=
ype (#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=3Dptest;
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 >=3D '2014-01-01'::date and dt < '20=
15-01-01'::date)) inherits (ptest.tbl);
create table ptest.tbl_p2015(check (dt >=3D '2015-01-01'::date and dt < '20=
16-01-01'::date)) inherits (ptest.tbl);

-- populate child partitions
with pd as ( delete from only ptest.tbl where dt >=3D '2014-01-01'::date an=
d dt < '2015-01-01'::date returning *)=20
insert into ptest.tbl_p2014 select * from pd;
with pd as ( delete from only ptest.tbl where dt >=3D '2015-01-01'::date an=
d dt < '2016-01-01'::date returning *)=20
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 =3D 'ptest' order by relname;

-- check that partitions show in parent
\d+ ptest.tbl

-- force constraint_exclusion to partition
set constraint_exclusion =3D partition;

-- #1: works
explain analyze select count(1) from ptest.tbl where dt =3D '2014-06-01'::d=
ate;

-- #2: works
explain analyze select count(1) from ptest.tbl where dt =3D DATE '2014-06-0=
1';

-- #3: full scan (no constraint exclusion)
explain analyze select count(1) from ptest.tbl where dt =3D to_date(201406:=
:text||01::text, 'YYYYMMDD');

-- #4: full scan (no constraint exclusion)
explain analyze select count(1) from ptest.tbl where dt =3D (select '2014-0=
6-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 =3D foo.dt);

</snip>

--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance