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

1 files changed, 30 insertions, 34 deletions

diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 994983b9164..185f0625a78 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -35,7 +35,6 @@
 #include "optimizer/planmain.h"
 #include "optimizer/planner.h"
 #include "optimizer/predtest.h"
-#include "optimizer/prep.h"
 #include "optimizer/restrictinfo.h"
 #include "optimizer/subselect.h"
 #include "optimizer/tlist.h"
@@ -494,8 +493,25 @@ create_scan_plan(PlannerInfo *root, Path *best_path, int flags)
 	 * Extract the relevant restriction clauses from the parent relation. The
 	 * executor must apply all these restrictions during the scan, except for
 	 * pseudoconstants which we'll take care of below.
+	 *
+	 * If this is a plain indexscan or index-only scan, we need not consider
+	 * restriction clauses that are implied by the index's predicate, so use
+	 * indrestrictinfo not baserestrictinfo.  Note that we can't do that for
+	 * bitmap indexscans, since there's not necessarily a single index
+	 * involved; but it doesn't matter since create_bitmap_scan_plan() will be
+	 * able to get rid of such clauses anyway via predicate proof.
 	 */
-	scan_clauses = rel->baserestrictinfo;
+	switch (best_path->pathtype)
+	{
+		case T_IndexScan:
+		case T_IndexOnlyScan:
+			Assert(IsA(best_path, IndexPath));
+			scan_clauses = ((IndexPath *) best_path)->indexinfo->indrestrictinfo;
+			break;
+		default:
+			scan_clauses = rel->baserestrictinfo;
+			break;
+	}
 
 	/*
 	 * If this is a parameterized scan, we also need to enforce all the join
@@ -2385,11 +2401,6 @@ create_indexscan_plan(PlannerInfo *root,
 	 * first input contains only immutable functions, so we have to check
 	 * that.)
 	 *
-	 * We can also discard quals that are implied by a partial index's
-	 * predicate, but only in a plain SELECT; when scanning a target relation
-	 * of UPDATE/DELETE/SELECT FOR UPDATE, we must leave such quals in the
-	 * plan so that they'll be properly rechecked by EvalPlanQual testing.
-	 *
 	 * Note: if you change this bit of code you should also look at
 	 * extract_nonindex_conditions() in costsize.c.
 	 */
@@ -2405,21 +2416,9 @@ create_indexscan_plan(PlannerInfo *root,
 			continue;			/* simple duplicate */
 		if (is_redundant_derived_clause(rinfo, indexquals))
 			continue;			/* derived from same EquivalenceClass */
-		if (!contain_mutable_functions((Node *) rinfo->clause))
-		{
-			List	   *clausel = list_make1(rinfo->clause);
-
-			if (predicate_implied_by(clausel, indexquals))
-				continue;		/* provably implied by indexquals */
-			if (best_path->indexinfo->indpred)
-			{
-				if (baserelid != root->parse->resultRelation &&
-					get_plan_rowmark(root->rowMarks, baserelid) == NULL)
-					if (predicate_implied_by(clausel,
-											 best_path->indexinfo->indpred))
-						continue;		/* implied by index predicate */
-			}
-		}
+		if (!contain_mutable_functions((Node *) rinfo->clause) &&
+			predicate_implied_by(list_make1(rinfo->clause), indexquals))
+			continue;			/* provably implied by indexquals */
 		qpqual = lappend(qpqual, rinfo);
 	}
 
@@ -2556,11 +2555,12 @@ create_bitmap_scan_plan(PlannerInfo *root,
 	 * redundant with any top-level indexqual by virtue of being generated
 	 * from the same EC.  After that, try predicate_implied_by().
 	 *
-	 * Unlike create_indexscan_plan(), we need take no special thought here
-	 * for partial index predicates; this is because the predicate conditions
-	 * are already listed in bitmapqualorig and indexquals.  Bitmap scans have
-	 * to do it that way because predicate conditions need to be rechecked if
-	 * the scan becomes lossy, so they have to be included in bitmapqualorig.
+	 * Unlike create_indexscan_plan(), the predicate_implied_by() test here is
+	 * useful for getting rid of qpquals that are implied by index predicates,
+	 * because the predicate conditions are included in the "indexquals"
+	 * returned by create_bitmap_subplan().  Bitmap scans have to do it that
+	 * way because predicate conditions need to be rechecked if the scan
+	 * becomes lossy, so they have to be included in bitmapqualorig.
 	 */
 	qpqual = NIL;
 	foreach(l, scan_clauses)
@@ -2575,13 +2575,9 @@ create_bitmap_scan_plan(PlannerInfo *root,
 			continue;			/* simple duplicate */
 		if (rinfo->parent_ec && list_member_ptr(indexECs, rinfo->parent_ec))
 			continue;			/* derived from same EquivalenceClass */
-		if (!contain_mutable_functions(clause))
-		{
-			List	   *clausel = list_make1(clause);
-
-			if (predicate_implied_by(clausel, indexquals))
-				continue;		/* provably implied by indexquals */
-		}
+		if (!contain_mutable_functions(clause) &&
+			predicate_implied_by(list_make1(clause), indexquals))
+			continue;			/* provably implied by indexquals */
 		qpqual = lappend(qpqual, rinfo);
 	}