Support using index-only scans with partial indexes in more cases.
Previously, the planner would reject an index-only scan if any restriction
clause for its table used a column not available from the index, even
if that restriction clause would later be dropped from the plan entirely
because it's implied by the index's predicate. This is a fairly common
situation for partial indexes because predicates using columns not included
in the index are often the most useful kind of predicate, and we have to
duplicate (or at least imply) the predicate in the WHERE clause in order
to get the index to be considered at all. So index-only scans were
essentially unavailable with such partial indexes.
To fix, we have to do detection of implied-by-predicate clauses much
earlier in the planner. This patch puts it in check_index_predicates
(nee check_partial_indexes), meaning it gets done for every partial index,
whereas we previously only considered this issue at createplan time,
so that the work was only done for an index actually selected for use.
That could result in a noticeable planning slowdown for queries against
tables with many partial indexes. However, testing suggested that there
isn't really a significant cost, especially not with reasonable numbers
of partial indexes. We do get a small additional benefit, which is that
cost_index is more accurate since it correctly discounts the evaluation
cost of clauses that will be removed. We can also avoid considering such
clauses as potential indexquals, which saves useless matching cycles in
the case where the predicate columns aren't in the index, and prevents
generating bogus plans that double-count the clause's selectivity when
the columns are in the index.
Tomas Vondra and Kyotaro Horiguchi, reviewed by Kevin Grittner and
Konstantin Knizhnik, and whacked around a little by me
Branch
------
master
Details
-------
http://git.postgresql.org/pg/commitdiff/f9aefcb91fc1f73fc43e384f660c120e515af931
Modified Files
--------------
src/backend/nodes/outfuncs.c | 1 +
src/backend/optimizer/path/allpaths.c | 4 +-
src/backend/optimizer/path/costsize.c | 23 +++--
src/backend/optimizer/path/indxpath.c | 123 +++++++++++++++++-------
src/backend/optimizer/plan/createplan.c | 64 ++++++-------
src/backend/optimizer/util/plancat.c | 3 +-
src/include/nodes/relation.h | 11 ++-
src/include/optimizer/paths.h | 2 +-
src/test/regress/expected/aggregates.out | 8 +-
src/test/regress/expected/select.out | 160 +++++++++++++++++++++++++++++++
src/test/regress/sql/select.sql | 44 +++++++++
11 files changed, 351 insertions(+), 92 deletions(-)