Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Nov 5, 2012 at 2:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Um, no. This is a useful counterexample:
>> WHERE t.a > x.c1 AND t.a < y.c2
> Well, OK. So maybe you also need the operator to be the same as well.
Nope. A counterexample to that claim is a GIN index on an array column:
WHERE t.arraycol @> array[1,2,3] AND t.arraycol @> array[4,5,6]
This restriction is equivalent to
WHERE t.arraycol @> array[1,2,3,4,5,6]
which is substantially more selective than either constraint alone.
If the two RHS arrays are not constants, but are coming from different
tables x and y, then we have something isomorphic to the previous
example (at least from the perspective of indxpath.c), but it would
not be good for indxpath.c to assume that these clauses couldn't be
useful together.
We *can* make a simplifying assumption of the kind you suggest when
we know that the clauses were all generated from the same equivalence
class, because then we have very strong assumptions about what the
clauses' semantics are. (And indeed the patch does take care of that
case separately.) But for the general case of non-equijoin clauses
we can't assume very much at all about whether clauses are redundant,
at least not without knowledge that indxpath.c hasn't got.
>> .... As patched, it will indeed limit what it considers
>> to at most one additional clause per index column, once it's hit the
>> heuristic limit --- but it's entirely possible for it to miss useful
>> combinations because of that.
> Seems unfortunate, but I don't understand the code well enough to know
> how to do better.
Me either. What I will say is that as patched, the code will still
find all useful clause combinations as long as there aren't too many
other relations involved. I've not been able to think of another way
of restricting the search that doesn't reject possibly-useful
combinations even in otherwise-very-simple queries.
regards, tom lane