Re: Partition Constraint Exclusion Limits

BTW: May be it could be feasible in future to perform partition exclusion
during the execution? This would be very neat feature.

Regards, Vitalii Tymchyshyn

Вт, 27 жовт. 2015 15:03 David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> пише:

> On Tue, Oct 27, 2015 at 2:29 PM, GMail <mfwilson(at)gmail(dot)com> wrote:
>
>> 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.
>
> David J.
>