Re: Extending constraint exclusion for implied constraints/conditions

On Tue, Jul 8, 2014 at 7:57 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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 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