aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/nodes/outfuncs.c1
-rw-r--r--src/backend/optimizer/path/allpaths.c4
-rw-r--r--src/backend/optimizer/path/costsize.c23
-rw-r--r--src/backend/optimizer/path/indxpath.c123
-rw-r--r--src/backend/optimizer/plan/createplan.c64
-rw-r--r--src/backend/optimizer/util/plancat.c3
-rw-r--r--src/include/nodes/relation.h11
-rw-r--r--src/include/optimizer/paths.h2
-rw-r--r--src/test/regress/expected/aggregates.out8
-rw-r--r--src/test/regress/expected/select.out160
-rw-r--r--src/test/regress/sql/select.sql44
11 files changed, 351 insertions, 92 deletions
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 5b71c95ede7..bfd12ac291a 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2129,6 +2129,7 @@ _outIndexOptInfo(StringInfo str, const IndexOptInfo *node)
/* indexprs is redundant since we print indextlist */
WRITE_NODE_FIELD(indpred);
WRITE_NODE_FIELD(indextlist);
+ WRITE_NODE_FIELD(indrestrictinfo);
WRITE_BOOL_FIELD(predOK);
WRITE_BOOL_FIELD(unique);
WRITE_BOOL_FIELD(immediate);
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index e1a5d339f2a..cc77ff9e1f0 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -474,7 +474,7 @@ set_plain_rel_size(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
* Test any partial indexes of rel for applicability. We must do this
* first since partial unique indexes can affect size estimates.
*/
- check_partial_indexes(root, rel);
+ check_index_predicates(root, rel);
/* Mark rel with estimated output rows, width, etc */
set_baserel_size_estimates(root, rel);
@@ -716,7 +716,7 @@ set_tablesample_rel_size(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
* Test any partial indexes of rel for applicability. We must do this
* first since partial unique indexes can affect size estimates.
*/
- check_partial_indexes(root, rel);
+ check_index_predicates(root, rel);
/*
* Call the sampling method's estimation function to estimate the number
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index b86fc5ed67a..b3957512741 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -433,15 +433,18 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count)
/*
* Mark the path with the correct row estimate, and identify which quals
- * will need to be enforced as qpquals.
+ * will need to be enforced as qpquals. We need not check any quals that
+ * are implied by the index's predicate, so we can use indrestrictinfo not
+ * baserestrictinfo as the list of relevant restriction clauses for the
+ * rel.
*/
if (path->path.param_info)
{
path->path.rows = path->path.param_info->ppi_rows;
/* qpquals come from the rel's restriction clauses and ppi_clauses */
qpquals = list_concat(
- extract_nonindex_conditions(baserel->baserestrictinfo,
- path->indexquals),
+ extract_nonindex_conditions(path->indexinfo->indrestrictinfo,
+ path->indexquals),
extract_nonindex_conditions(path->path.param_info->ppi_clauses,
path->indexquals));
}
@@ -449,7 +452,7 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count)
{
path->path.rows = baserel->rows;
/* qpquals come from just the rel's restriction clauses */
- qpquals = extract_nonindex_conditions(baserel->baserestrictinfo,
+ qpquals = extract_nonindex_conditions(path->indexinfo->indrestrictinfo,
path->indexquals);
}
@@ -631,11 +634,11 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count)
* final plan. So we approximate it as quals that don't appear directly in
* indexquals and also are not redundant children of the same EquivalenceClass
* as some indexqual. This method neglects some infrequently-relevant
- * considerations such as clauses that needn't be checked because they are
- * implied by a partial index's predicate. It does not seem worth the cycles
- * to try to factor those things in at this stage, even though createplan.c
- * will take pains to remove such unnecessary clauses from the qpquals list if
- * this path is selected for use.
+ * considerations, specifically clauses that needn't be checked because they
+ * are implied by an indexqual. It does not seem worth the cycles to try to
+ * factor that in at this stage, even though createplan.c will take pains to
+ * remove such unnecessary clauses from the qpquals list if this path is
+ * selected for use.
*/
static List *
extract_nonindex_conditions(List *qual_clauses, List *indexquals)
@@ -654,7 +657,7 @@ extract_nonindex_conditions(List *qual_clauses, List *indexquals)
continue; /* simple duplicate */
if (is_redundant_derived_clause(rinfo, indexquals))
continue; /* derived from same EquivalenceClass */
- /* ... skip the predicate proof attempts createplan.c will try ... */
+ /* ... skip the predicate proof attempt createplan.c will try ... */
result = lappend(result, rinfo);
}
return result;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index b48f5f28ea9..2952bfb7c22 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -30,6 +30,7 @@
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
#include "optimizer/predtest.h"
+#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "optimizer/var.h"
#include "utils/builtins.h"
@@ -216,7 +217,7 @@ static Const *string_to_const(const char *str, Oid datatype);
*
* 'rel' is the relation for which we want to generate index paths
*
- * Note: check_partial_indexes() must have been run previously for this rel.
+ * Note: check_index_predicates() must have been run previously for this rel.
*
* Note: in cases involving LATERAL references in the relation's tlist, it's
* possible that rel->lateral_relids is nonempty. Currently, we include
@@ -1800,25 +1801,27 @@ check_index_only(RelOptInfo *rel, IndexOptInfo *index)
/*
* Check that all needed attributes of the relation are available from the
* index.
- *
- * XXX this is overly conservative for partial indexes, since we will
- * consider attributes involved in the index predicate as required even
- * though the predicate won't need to be checked at runtime. (The same is
- * true for attributes used only in index quals, if we are certain that
- * the index is not lossy.) However, it would be quite expensive to
- * determine that accurately at this point, so for now we take the easy
- * way out.
*/
/*
- * Add all the attributes needed for joins or final output. Note: we must
- * look at rel's targetlist, not the attr_needed data, because attr_needed
- * isn't computed for inheritance child rels.
+ * First, identify all the attributes needed for joins or final output.
+ * Note: we must look at rel's targetlist, not the attr_needed data,
+ * because attr_needed isn't computed for inheritance child rels.
*/
pull_varattnos((Node *) rel->reltarget->exprs, rel->relid, &attrs_used);
- /* Add all the attributes used by restriction clauses. */
- foreach(lc, rel->baserestrictinfo)
+ /*
+ * Add all the attributes used by restriction clauses; but consider only
+ * those clauses not implied by the index predicate, since ones that are
+ * so implied don't need to be checked explicitly in the plan.
+ *
+ * Note: attributes used only in index quals would not be needed at
+ * runtime either, if we are certain that the index is not lossy. However
+ * it'd be complicated to account for that accurately, and it doesn't
+ * matter in most cases, since we'd conclude that such attributes are
+ * available from the index anyway.
+ */
+ foreach(lc, index->indrestrictinfo)
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
@@ -2023,7 +2026,8 @@ static void
match_restriction_clauses_to_index(RelOptInfo *rel, IndexOptInfo *index,
IndexClauseSet *clauseset)
{
- match_clauses_to_index(index, rel->baserestrictinfo, clauseset);
+ /* We can ignore clauses that are implied by the index predicate */
+ match_clauses_to_index(index, index->indrestrictinfo, clauseset);
}
/*
@@ -2664,39 +2668,48 @@ match_clause_to_ordering_op(IndexOptInfo *index,
****************************************************************************/
/*
- * check_partial_indexes
- * Check each partial index of the relation, and mark it predOK if
- * the index's predicate is satisfied for this query.
+ * check_index_predicates
+ * Set the predicate-derived IndexOptInfo fields for each index
+ * of the specified relation.
+ *
+ * predOK is set true if the index is partial and its predicate is satisfied
+ * for this query, ie the query's WHERE clauses imply the predicate.
*
- * Note: it is possible for this to get re-run after adding more restrictions
- * to the rel; so we might be able to prove more indexes OK. We assume that
- * adding more restrictions can't make an index not OK.
+ * indrestrictinfo is set to the relation's baserestrictinfo list less any
+ * conditions that are implied by the index's predicate. (Obviously, for a
+ * non-partial index, this is the same as baserestrictinfo.) Such conditions
+ * can be dropped from the plan when using the index, in certain cases.
+ *
+ * At one time it was possible for this to get re-run after adding more
+ * restrictions to the rel, thus possibly letting us prove more indexes OK.
+ * That doesn't happen any more (at least not in the core code's usage),
+ * but this code still supports it in case extensions want to mess with the
+ * baserestrictinfo list. We assume that adding more restrictions can't make
+ * an index not predOK. We must recompute indrestrictinfo each time, though,
+ * to make sure any newly-added restrictions get into it if needed.
*/
void
-check_partial_indexes(PlannerInfo *root, RelOptInfo *rel)
+check_index_predicates(PlannerInfo *root, RelOptInfo *rel)
{
List *clauselist;
bool have_partial;
+ bool is_target_rel;
Relids otherrels;
ListCell *lc;
/*
- * Frequently, there will be no partial indexes, so first check to make
- * sure there's something useful to do here.
+ * Initialize the indrestrictinfo lists to be identical to
+ * baserestrictinfo, and check whether there are any partial indexes. If
+ * not, this is all we need to do.
*/
have_partial = false;
foreach(lc, rel->indexlist)
{
IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
- if (index->indpred == NIL)
- continue; /* ignore non-partial indexes */
-
- if (index->predOK)
- continue; /* don't repeat work if already proven OK */
-
- have_partial = true;
- break;
+ index->indrestrictinfo = rel->baserestrictinfo;
+ if (index->indpred)
+ have_partial = true;
}
if (!have_partial)
return;
@@ -2743,18 +2756,54 @@ check_partial_indexes(PlannerInfo *root, RelOptInfo *rel)
otherrels,
rel));
- /* Now try to prove each index predicate true */
+ /*
+ * Normally we remove quals that are implied by a partial index's
+ * predicate from indrestrictinfo, indicating that they need not be
+ * checked explicitly by an indexscan plan using this index. However, if
+ * the rel is a target relation of UPDATE/DELETE/SELECT FOR UPDATE, we
+ * cannot remove such quals from the plan, because they need to be in the
+ * plan so that they will be properly rechecked by EvalPlanQual testing.
+ * Some day we might want to remove such quals from the main plan anyway
+ * and pass them through to EvalPlanQual via a side channel; but for now,
+ * we just don't remove implied quals at all for target relations.
+ */
+ is_target_rel = (rel->relid == root->parse->resultRelation ||
+ get_plan_rowmark(root->rowMarks, rel->relid) != NULL);
+
+ /*
+ * Now try to prove each index predicate true, and compute the
+ * indrestrictinfo lists for partial indexes. Note that we compute the
+ * indrestrictinfo list even for non-predOK indexes; this might seem
+ * wasteful, but we may be able to use such indexes in OR clauses, cf
+ * generate_bitmap_or_paths().
+ */
foreach(lc, rel->indexlist)
{
IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
+ ListCell *lcr;
if (index->indpred == NIL)
- continue; /* ignore non-partial indexes */
+ continue; /* ignore non-partial indexes here */
- if (index->predOK)
- continue; /* don't repeat work if already proven OK */
+ if (!index->predOK) /* don't repeat work if already proven OK */
+ index->predOK = predicate_implied_by(index->indpred, clauselist);
- index->predOK = predicate_implied_by(index->indpred, clauselist);
+ /* If rel is an update target, leave indrestrictinfo as set above */
+ if (is_target_rel)
+ continue;
+
+ /* Else compute indrestrictinfo as the non-implied quals */
+ index->indrestrictinfo = NIL;
+ foreach(lcr, rel->baserestrictinfo)
+ {
+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(lcr);
+
+ /* predicate_implied_by() assumes first arg is immutable */
+ if (contain_mutable_functions((Node *) rinfo->clause) ||
+ !predicate_implied_by(list_make1(rinfo->clause),
+ index->indpred))
+ index->indrestrictinfo = lappend(index->indrestrictinfo, rinfo);
+ }
}
}
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);
}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 546067b064f..5bdeac0df64 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -339,7 +339,8 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
/* Build targetlist using the completed indexprs data */
info->indextlist = build_index_tlist(root, info, relation);
- info->predOK = false; /* set later in indxpath.c */
+ info->indrestrictinfo = NIL; /* set later, in indxpath.c */
+ info->predOK = false; /* set later, in indxpath.c */
info->unique = index->indisunique;
info->immediate = index->indimmediate;
info->hypothetical = false;
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 641446ca712..d39c73b8b9a 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -563,6 +563,10 @@ typedef struct RelOptInfo
* indextlist is a TargetEntry list representing the index columns.
* It provides an equivalent base-relation Var for each simple column,
* and links to the matching indexprs element for each expression column.
+ *
+ * While most of these fields are filled when the IndexOptInfo is created
+ * (by plancat.c), indrestrictinfo and predOK are set later, in
+ * check_index_predicates().
*/
typedef struct IndexOptInfo
{
@@ -595,7 +599,12 @@ typedef struct IndexOptInfo
List *indextlist; /* targetlist representing index columns */
- bool predOK; /* true if predicate matches query */
+ List *indrestrictinfo;/* parent relation's baserestrictinfo list,
+ * less any conditions implied by the index's
+ * predicate (unless it's a target rel, see
+ * comments in check_index_predicates()) */
+
+ bool predOK; /* true if index predicate matches query */
bool unique; /* true if a unique index */
bool immediate; /* is uniqueness enforced immediately? */
bool hypothetical; /* true if index doesn't really exist */
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 2fccc3a998a..7ad4f026a36 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -70,7 +70,7 @@ extern bool match_index_to_operand(Node *operand, int indexcol,
extern void expand_indexqual_conditions(IndexOptInfo *index,
List *indexclauses, List *indexclausecols,
List **indexquals_p, List **indexqualcols_p);
-extern void check_partial_indexes(PlannerInfo *root, RelOptInfo *rel);
+extern void check_index_predicates(PlannerInfo *root, RelOptInfo *rel);
extern Expr *adjust_rowcompare_for_index(RowCompareExpr *clause,
IndexOptInfo *index,
int indexcol,
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 601bdb405aa..3ff669140ff 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -780,7 +780,6 @@ explain (costs off)
-> Index Only Scan Backward using minmaxtest2i on minmaxtest2
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest3i on minmaxtest3
- Index Cond: (f1 IS NOT NULL)
InitPlan 2 (returns $1)
-> Limit
-> Merge Append
@@ -792,8 +791,7 @@ explain (costs off)
-> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
- Index Cond: (f1 IS NOT NULL)
-(25 rows)
+(23 rows)
select min(f1), max(f1) from minmaxtest;
min | max
@@ -818,7 +816,6 @@ explain (costs off)
-> Index Only Scan Backward using minmaxtest2i on minmaxtest2
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan using minmaxtest3i on minmaxtest3
- Index Cond: (f1 IS NOT NULL)
InitPlan 2 (returns $1)
-> Limit
-> Merge Append
@@ -830,11 +827,10 @@ explain (costs off)
-> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1
Index Cond: (f1 IS NOT NULL)
-> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
- Index Cond: (f1 IS NOT NULL)
-> Sort
Sort Key: ($0), ($1)
-> Result
-(28 rows)
+(26 rows)
select distinct min(f1), max(f1) from minmaxtest;
min | max
diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out
index c376523bbe3..f84f8ac767d 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -734,6 +734,166 @@ SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
(7 rows)
--
+-- Test planning of some cases with partial indexes
+--
+-- partial index is usable
+explain (costs off)
+select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+ QUERY PLAN
+-----------------------------------------
+ Index Scan using onek2_u2_prtl on onek2
+ Index Cond: (unique2 = 11)
+ Filter: (stringu1 = 'ATAAAA'::name)
+(3 rows)
+
+select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+ 494 | 11 | 0 | 2 | 4 | 14 | 4 | 94 | 94 | 494 | 494 | 8 | 9 | ATAAAA | LAAAAA | VVVVxx
+(1 row)
+
+explain (costs off)
+select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+ QUERY PLAN
+-----------------------------------------
+ Index Scan using onek2_u2_prtl on onek2
+ Index Cond: (unique2 = 11)
+ Filter: (stringu1 = 'ATAAAA'::name)
+(3 rows)
+
+select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+ unique2
+---------
+ 11
+(1 row)
+
+-- partial index predicate implies clause, so no need for retest
+explain (costs off)
+select * from onek2 where unique2 = 11 and stringu1 < 'B';
+ QUERY PLAN
+-----------------------------------------
+ Index Scan using onek2_u2_prtl on onek2
+ Index Cond: (unique2 = 11)
+(2 rows)
+
+select * from onek2 where unique2 = 11 and stringu1 < 'B';
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+ 494 | 11 | 0 | 2 | 4 | 14 | 4 | 94 | 94 | 494 | 494 | 8 | 9 | ATAAAA | LAAAAA | VVVVxx
+(1 row)
+
+explain (costs off)
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+ QUERY PLAN
+----------------------------------------------
+ Index Only Scan using onek2_u2_prtl on onek2
+ Index Cond: (unique2 = 11)
+(2 rows)
+
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+ unique2
+---------
+ 11
+(1 row)
+
+-- but if it's an update target, must retest anyway
+explain (costs off)
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update;
+ QUERY PLAN
+-----------------------------------------------
+ LockRows
+ -> Index Scan using onek2_u2_prtl on onek2
+ Index Cond: (unique2 = 11)
+ Filter: (stringu1 < 'B'::name)
+(4 rows)
+
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update;
+ unique2
+---------
+ 11
+(1 row)
+
+-- partial index is not applicable
+explain (costs off)
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'C';
+ QUERY PLAN
+-------------------------------------------------------
+ Seq Scan on onek2
+ Filter: ((stringu1 < 'C'::name) AND (unique2 = 11))
+(2 rows)
+
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'C';
+ unique2
+---------
+ 11
+(1 row)
+
+-- partial index implies clause, but bitmap scan must recheck predicate anyway
+SET enable_indexscan TO off;
+explain (costs off)
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+ QUERY PLAN
+-------------------------------------------------------------
+ Bitmap Heap Scan on onek2
+ Recheck Cond: ((unique2 = 11) AND (stringu1 < 'B'::name))
+ -> Bitmap Index Scan on onek2_u2_prtl
+ Index Cond: (unique2 = 11)
+(4 rows)
+
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+ unique2
+---------
+ 11
+(1 row)
+
+RESET enable_indexscan;
+-- check multi-index cases too
+explain (costs off)
+select unique1, unique2 from onek2
+ where (unique2 = 11 or unique1 = 0) and stringu1 < 'B';
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Bitmap Heap Scan on onek2
+ Recheck Cond: (((unique2 = 11) AND (stringu1 < 'B'::name)) OR (unique1 = 0))
+ Filter: (stringu1 < 'B'::name)
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_u2_prtl
+ Index Cond: (unique2 = 11)
+ -> Bitmap Index Scan on onek2_u1_prtl
+ Index Cond: (unique1 = 0)
+(8 rows)
+
+select unique1, unique2 from onek2
+ where (unique2 = 11 or unique1 = 0) and stringu1 < 'B';
+ unique1 | unique2
+---------+---------
+ 494 | 11
+ 0 | 998
+(2 rows)
+
+explain (costs off)
+select unique1, unique2 from onek2
+ where (unique2 = 11 and stringu1 < 'B') or unique1 = 0;
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Bitmap Heap Scan on onek2
+ Recheck Cond: (((unique2 = 11) AND (stringu1 < 'B'::name)) OR (unique1 = 0))
+ -> BitmapOr
+ -> Bitmap Index Scan on onek2_u2_prtl
+ Index Cond: (unique2 = 11)
+ -> Bitmap Index Scan on onek2_u1_prtl
+ Index Cond: (unique1 = 0)
+(7 rows)
+
+select unique1, unique2 from onek2
+ where (unique2 = 11 and stringu1 < 'B') or unique1 = 0;
+ unique1 | unique2
+---------+---------
+ 494 | 11
+ 0 | 998
+(2 rows)
+
+--
-- Test some corner cases that have been known to confuse the planner
--
-- ORDER BY on a constant doesn't really need any sorting
diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql
index b99fb13c7d3..abdd785a770 100644
--- a/src/test/regress/sql/select.sql
+++ b/src/test/regress/sql/select.sql
@@ -188,6 +188,50 @@ SELECT * FROM foo ORDER BY f1 DESC;
SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
--
+-- Test planning of some cases with partial indexes
+--
+
+-- partial index is usable
+explain (costs off)
+select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+explain (costs off)
+select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+-- partial index predicate implies clause, so no need for retest
+explain (costs off)
+select * from onek2 where unique2 = 11 and stringu1 < 'B';
+select * from onek2 where unique2 = 11 and stringu1 < 'B';
+explain (costs off)
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+-- but if it's an update target, must retest anyway
+explain (costs off)
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update;
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update;
+-- partial index is not applicable
+explain (costs off)
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'C';
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'C';
+-- partial index implies clause, but bitmap scan must recheck predicate anyway
+SET enable_indexscan TO off;
+explain (costs off)
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+RESET enable_indexscan;
+-- check multi-index cases too
+explain (costs off)
+select unique1, unique2 from onek2
+ where (unique2 = 11 or unique1 = 0) and stringu1 < 'B';
+select unique1, unique2 from onek2
+ where (unique2 = 11 or unique1 = 0) and stringu1 < 'B';
+explain (costs off)
+select unique1, unique2 from onek2
+ where (unique2 = 11 and stringu1 < 'B') or unique1 = 0;
+select unique1, unique2 from onek2
+ where (unique2 = 11 and stringu1 < 'B') or unique1 = 0;
+
+--
-- Test some corner cases that have been known to confuse the planner
--
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-30 19:55:42 +0000