Re: Extending constraint exclusion for implied constraints/conditions

Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> writes:
> On Mon, Jul 7, 2014 at 7:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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 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.

regards, tom lane