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.
Short answer, no.
The planner has the responsibility for performing constraint exclusion and it only has access to constants during its evaluation. It has no clue what kind of transformations a function might do. Various other optimizations are indeed possible but are not presently performed.
So, #3 (
to_date(201406::text||01::text, 'YYYYMMDD');
)
is down-right impossible given the present architecture
; and likely any future architecture.
With #4 (
explain analyze select count(1) from ptest.tbl where dt = (select '2014-06-01'::date);
)
in theory the re-write module could recognize and re-write this remove the sub-select.
But likely real-life is not so simple otherwise the query writer likely would have simply done is directly themself.
In a partitioning scheme the partitioning data has to be injected into the query explicitly so that it is already in place before the planner receives the query. Anything within the query requiring "execution" is handled by the executor and at that point the chance to exclude partitions has come and gone.
