Re: "Constraint exclusion" is not general enough - Mailing list pgsql-hackers

On Fri, 2006-08-04 at 14:40 -0400, Tom Lane wrote:
> I was just looking at Martin Lesser's gripe here:
> http://archives.postgresql.org/pgsql-performance/2006-08/msg00053.php
> about how the planner is not real bright about the filter conditions
> it generates for a simple partitioning layout.  In particular it's
> generating scans involving self-contradictory conditions:
> 
>  Result  (cost=0.00..33.20 rows=6 width=36)
>    ->  Append  (cost=0.00..33.20 rows=6 width=36)
>          ->  Seq Scan on t_parted  (cost=0.00..33.20 rows=6 width=36)
>                Filter: ((id1 >= 0) AND (id1 < 100) AND (id1 >= 900) AND (id1 < 1000))
> 
> which it seems we ought to be bright enough to notice.  In particular
> I would argue that turning on constraint_exclusion ought to instruct
> the planner to catch this sort of thing, whereas when it's off we
> ought not expend the cycles.  I have a preliminary patch (below)
> that seems to fix it.
> 
> The problem I'm having is that this isn't "constraint exclusion" anymore
> --- it will in fact make useful deductions without a table constraint
> anywhere in sight.  Should we rename the GUC variable, and if so to what?
> Or just live with the misnomer?  I guess plan C would be to invent a
> separate GUC variable for the other kind of test, but I can't see that
> it's worth having two.  Thoughts?

In general, I'd prefer a control that allowed "amount of planning" to be
specified, much in the same way we rate error messages. We really want
just one simple knob that can be turned up or down, no matter how many
new optimizations we add.

planning_effort = LOW | MEDIUM | HIGH | VERYHIGH | EXHAUSTIVE

Though I'd prefer about 6-10 settings

> BTW, the remaining infelicities in Martin's example stem from the fact that
> predicate_refuted_by doesn't recognize "x IS NOT TRUE" as refuting "x".
> Working on fixing that now.

Seen that. Looks good.

> I'm also thinking that formulating the check as "constraints are
> refuted by WHERE clause" might be unnecessarily restrictive.  Doesn't
> the case where a constraint implies falsity of a WHERE clause likewise
> tell us we needn't bother to scan?  Seems like we ought to put all the
> conditions together and run a symmetric test named something like
> "mutually_exclusive_conditions".  Maybe "mutual_exclusion" would be
> a better name for the GUC variable.

That would be another approach to planning this, but I see a future that
may interfere with that suggestion (maybe not, so read on).

Currently, exclusion is tested for each relation that might possibly be
involved, so planning time increases O(N) for N partitions. I would like
to see a declarative approach where the constraints on all of the child
partitions form a coherent pattern that can be used to process far fewer
exclusion tests to enable us to achieve O(logN) behaviour. I'm not sure
how we'd do that if we went for the symmetric test approach.

To achieve the "indexed" partition pruning, we'd need
- a way to specify that all constraints are mutually exclusive
- a declarative approach for saying something like "arranged in date
sequence"
- preferably a way to have this happen at run-time so we can hard-plan a
query with CURRENT_TIMESTAMP in the WHERE clause

The reason for all of that is to better enable partitioning for use in
OLTP situations, not just DW.

With all of that in mind, I think renaming the current GUC would be a
fairly short-lived name change, so I'd suggest sticking with it until
the functions have been enhanced to the point we can dream up a slightly
more evocative name.

--  Simon Riggs EnterpriseDB          http://www.enterprisedb.com