Re: Generalizing range-constraint detection in clauselist_selectivity

> I'm thinking that this is overly restrictive, and we could usefully
> suppose that "var >= anything" and "var <= anything" should be treated
> as a range constraint pair if the vars match and there are no volatile
> functions in the expressions. We are only trying to get a selectivity
> estimate here, so rigorous correctness is not required. However, I'm
> a little worried that I might be overlooking cases where this would be
> unduly optimistic. Does anyone see a situation where such a pair of
> clauses *shouldn't* be thought to be a range constraint on the var?
> For instance, should we still restrict the "var" side to be an
> expression in columns of only one relation?

Hmmm. I don't see why we have to restrict them, at least in theory.
If more than one relation is involved in an expression for "var", then
doesn't the join between the other relations have to be evaluated prior
to evaluating the join conditions on the range relation? i.e. it seems
to me that for relations a,b,c:

where
( a.1 + b.1 ) <= c.1 and ( a.2 + b.2 ) >= c.1

... that we're already forced to join a and b before we can meaningfully
evaluate the join condition on c, no? If not, then we do have to
restrict, but it seems to me that we are.

Other than that, I can't come up with a real problem for this
optimization which wouldn't already be disqualified (like types which
evaluate >= in a non-scalar manner).

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com