>> I doubt it. The extra code isn't the problem so much, it's the extra >> planning cycles spent trying to make proofs that will usually fail. >> What you propose will create a combinatorial explosion in the number >> of proof paths to be considered.
> I can understand that it will create combinatorial explosion in the number > of predicates that need to be examined by the constraint exclusion. I do > not understand where come the paths gets involved here. The constraint > exclusion kicks in before paths are created
Perhaps I should not have used the term "path" here, because I wasn't referring to what the planner calls Paths. I just meant that there will be many more ways to perhaps prove a constraint-exclusion result, and the planner will have to go through them all.
(Usually to no avail, because how often do people write queries that are guaranteed to produce no rows?)
An example of what I meant by combinatorial explosion is that if a clause mentions K variables, and each of those variables has been equated to M other variables, there are (M+1)^K possible derived clauses, and it looks to me like we'd have to consider them all to catch the sort of situation you're talking about.
I agree.
I think this is going to require a *lot* of additional planner cycles, and TBH you've not provided any very compelling example of why it's worthwhile. Yeah, if you can exclude a large partition it's a win, but how often is that going to happen in real-world queries? The one example you gave seemed pretty artificial to me, ie unrelated to typical partitioning constraints.
Yes, the example is a "cooked" one to show the problem. But the case can be encountered easily in partitioned tables. A partitioned table (having constraints on each partition) with few table level constraints, can undergo queries with some clauses in WHERE clause which yield empty results for one or more partitions. Saving some table scans would be worth the time spent in bringing up (M+1)^K derived clauses and examining those. Greg's example about parallel joins or joins between partitioned foreign tables would yield much better results, if we have this optimization. But, I think, I will wait till parallel joins or partitioned foreign tables is a reality in PostgreSQL.
regards, tom lane
--
Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
