Hi,
On 07/11/2015 11:40 PM, Tom Lane wrote:
Tomas Vondra <tomas.von...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
