Re: Partitioning and constraint exclusion

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