Re: strange plan with bitmap heap scan and multiple partial indexes

Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
> So I think the predicate proofing is a better approach, but of course
> the planning cost may be an issue. But maybe we can make this cheaper by
> some clever tricks? For example, given two predicates A and B, it seems
> that if A => B, then selectivity(A) <= selectivity(B). Could we use this
> to skip some of the expensive stuff? We should have the selectivities
> anyway, no?

We do. The existing logic in choose_bitmap_and essentially uses the
selectivity as a heuristic to indicate which partial indexes might have
predicates that imply another index's predicate. The expectation is that
the former would have selectivity strictly smaller than the latter,
causing the former to be processed first, and then the existing rules
about what indexes can be "added onto" a potential AND combination will
do the trick.

The reason this fails in your example is that if the two indexes have
exactly identical selectivities (due to identical reltuples values),
there's no certainty what order they get sorted in, and the adding-on
rules don't catch the case where the new index would actually imply
the old one rather than vice versa.

Conceivably, we could fix this at relatively small cost in the normal case
by considering predicate proof rules in the sort comparator, and only if
the estimated selectivities are identical. Sure seems like a kluge
though, and I remain unconvinced that it's really a case that arises that
much in the real world. The short description of the set of indexes you
showed is "redundantly silly". Useful sets of indexes would not likely
all have indistinguishably small selectivities.

Perhaps a less klugy answer is to tweak the adding-on rules some more,
but I haven't thought about exactly how.

regards, tom lane