On 03/04/2015 12:11 AM, David G Johnston wrote:
In short - since the planner determines exclusion constraints and the executor, which strictly follows the planner in the query execution process, would be the one to determine what the value of your date is - there is no way for a single query to provide data that would then be used to determine exclusion constraints. Now, that said, I don't believe you should be actual full table scans during processing if you have proper indexes setup. An index scan should be usable and quickly determine which tables lack data to contribute to the query results. In terms of separating out the date query and partition query: PREPARE/EXECUTE in pure SQL (can, must?, be direct) EXECUTE/USING in pl/pgsql (via a function) You may have other reasonable options on the client side... You should consider providing EXPLAIN ANALYZE results and maybe a test case so others can give pointers. David J.
Just to correct, the constraint column is also an indexed column. What I meant by "scanning all partitions" was that the index of all the partitions was being checked. Not the biggest issue, but still unwanted.
I spent most of yesterday comparing how DB2 handles table partitions and running similar explain queries. It indeed can do partition elimination when the values of the constraint column are coming from a sub query or other none constant value.
Does anyone know if there is a wishlist item for improving this in postgresql or is this as good as it gets for now?
I can work around the issues, but it just kind of caught me off guard since there are so many nice benchmarks on the web with partitioning in postgresql (and now that I go back an look at them, they all have constants in the where clause).
--Sam -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
