Hi,
On 07/11/2015 11:40 PM, Tom Lane wrote:
> Tomas Vondra <tomas.vondra@2ndquadrant.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.
Ah, OK. Thanks for the explanation.
> 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.
Yep, that's what basically I had in mind.
> 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.
Fair point - the example really is artificial, and was constructed to
demonstrate planning costs of the extended predicate-proofing for
partial indexes. And while in reality small partial indexes are quite
common, they probably use predicates tailored for individual queries
(and not the nested predicates as in the example).
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services