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.g.johns...@gmail.com> пише: > On Tue, Oct 27, 2015 at 2:29 PM, GMail <mfwil...@gmail.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. >
