On 7/15/23 16:20, Tomas Vondra wrote:
>
> ...
>
> 4) problems with opcintype != opckeytype (name_ops)
>
> While running the tests, I ran into an issue with name_ops, causing
> failures for \dT and other catalog queries. The root cause is that
> name_ops has opcintype = name, but opckeytype = cstring. The index-only
> clauses are copied from the table, with Vars mutated to reference the
> INDEX_VAR. But the type is not, so when we get to evaluating the
> expressions, CheckVarSlotCompatibility() fails because the Var has name,
> but the iss_IndexSlot (created with index tuple descriptor) has cstring.
>
> The rebased patch fixes this by explicitly adjusting types of the
> descriptor in ExecInitIndexScan().
>
> However, maybe this indicates the very idea of evaluating expressions
> using slot with index tuple descriptor is misguided. This made me look
> at regular index-only scan (nodeIndexonlyscan.c), and that uses a slot
> with the "table" structure, and instead of evaluating the expression on
> the index index tuple it expands the index tuple into the table slot.
> Which is what StoreIndexTuple() does.
>
> So maybe this should do what IOS does - expand the index tuple into
> "table slot" and evaluate the expression on that. That'd also make the
> INDEX_VAR tweak in createplan.c unnecessary - in fact, that seemed a bit
> strange anyway, so ditching fix_indexfilter_mutator would be good.
>
This kept bothering me, so I looked at it today, and reworked it to use
the IOS approach. It's a bit more complicated because for IOS both slots
have the same overall structure, except for the data types. But for
regular index scans that's not the case - the code has to "expand" the
index tuple into the larger "table slot". This works, and in general I
think the result is much cleaner - in particular, it means we don't need
to switch the Var nodes to reference the INDEX_VAR.
While experimenting with this I realized again that we're not matching
expressions to IOS. So if you have an expression index on (a+b), that
can't be used even if the query only uses this particular expression.
The same limitation applies to index-only filters, of course. It's not
the fault of this patch, but perhaps it'd be an interesting improvement.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From 685e4014533d9d5f29564a3a21b972abba70ee9f Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.von...@postgresql.org>
Date: Sun, 9 Apr 2023 02:08:45 +0200
Subject: [PATCH] evaluate filters on the index tuple (when possible)
Discussion: https://www.postgresql.org/message-id/flat/N1xaIrU29uk5YxLyW55MGk5fz9s6V2FNtj54JRaVlFbPixD5z8sJ07Ite5CvbWwik8ZvDG07oSTN-usENLVMq2UAcizVTEd5b-o16ZGDIIU%3D%40yamlcoder.me
---
src/backend/commands/explain.c | 2 +
src/backend/executor/nodeIndexscan.c | 258 +++++++++++++++++-
src/backend/optimizer/path/costsize.c | 23 ++
src/backend/optimizer/path/indxpath.c | 190 +++++++++++--
src/backend/optimizer/plan/createplan.c | 80 ++++++
src/backend/optimizer/plan/planner.c | 2 +-
src/backend/optimizer/plan/setrefs.c | 6 +
src/backend/optimizer/util/pathnode.c | 2 +
src/backend/utils/misc/guc_tables.c | 10 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/nodes/execnodes.h | 7 +
src/include/nodes/pathnodes.h | 1 +
src/include/nodes/plannodes.h | 2 +
src/include/optimizer/cost.h | 1 +
src/include/optimizer/pathnode.h | 1 +
src/test/regress/expected/create_index.out | 19 +-
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/expected/updatable_views.out | 12 +-
18 files changed, 580 insertions(+), 40 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 8570b14f621..556388bc499 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1778,6 +1778,8 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_IndexScan:
show_scan_qual(((IndexScan *) plan)->indexqualorig,
"Index Cond", planstate, ancestors, es);
+ show_scan_qual(((IndexScan *) plan)->indexfiltersorig,
+ "Index Filter", planstate, ancestors, es);
if (((IndexScan *) plan)->indexqualorig)
show_instrumentation_count("Rows Removed by Index Recheck", 2,
planstate, es);
diff --git a/src/backend/executor/nodeIndexscan.c b/src/backend/executor/nodeIndexscan.c
index 4540c7781d2..5ee1fd86d7f 100644
--- a/src/backend/executor/nodeIndexscan.c
+++ b/src/backend/executor/nodeIndexscan.c
@@ -32,6 +32,8 @@
#include "access/nbtree.h"
#include "access/relscan.h"
#include "access/tableam.h"
+#include "access/visibilitymap.h"
+#include "catalog/index.h"
#include "catalog/pg_am.h"
#include "executor/execdebug.h"
#include "executor/nodeIndexscan.h"
@@ -69,6 +71,10 @@ static void reorderqueue_push(IndexScanState *node, TupleTableSlot *slot,
Datum *orderbyvals, bool *orderbynulls);
static HeapTuple reorderqueue_pop(IndexScanState *node);
+static void StoreIndexTuple(TupleTableSlot *slot, IndexTuple itup,
+ TupleDesc itupdesc, IndexInfo *iinfo);
+static void StoreHeapTuple(TupleTableSlot *slot, HeapTuple tup,
+ TupleDesc tupdesc, IndexInfo *iinfo);
/* ----------------------------------------------------------------
* IndexNext
@@ -115,6 +121,13 @@ IndexNext(IndexScanState *node)
node->iss_ScanDesc = scandesc;
+ /* Set it up for index-only filters, if there are any. */
+ if (node->indexfilters != NULL)
+ {
+ node->iss_ScanDesc->xs_want_itup = true;
+ node->iss_VMBuffer = InvalidBuffer;
+ }
+
/*
* If no run-time keys to calculate or they are ready, go ahead and
* pass the scankeys to the index AM.
@@ -127,11 +140,150 @@ IndexNext(IndexScanState *node)
/*
* ok, now that we have what we need, fetch the next tuple.
+ *
+ * XXX maybe we should invent something like index_getnext_tid/index_getnext_slot
+ * that would allow doing this in a more readable / coherent way.
*/
- while (index_getnext_slot(scandesc, direction, slot))
+ while (true)
{
+ bool has_index_tuple = false;
+ bool filter_checked;
+
CHECK_FOR_INTERRUPTS();
+ /*
+ * XXX code from index_getnext_slot(), but we need to inject stuff between
+ * the index_getnext_tid() and index_fetch_heap(), so we do it here
+ */
+ for (;;)
+ {
+ filter_checked = false;
+
+ if (!scandesc->xs_heap_continue)
+ {
+ ItemPointer tid;
+
+ /* Time to fetch the next TID from the index */
+ tid = index_getnext_tid(scandesc, direction);
+
+ /* If we're out of index entries, we're done */
+ if (tid == NULL)
+ break;
+
+ Assert(ItemPointerEquals(tid, &scandesc->xs_heaptid));
+ }
+
+ /* Make sure we have a valid item pointer. */
+ Assert(ItemPointerIsValid(&scandesc->xs_heaptid));
+
+ /*
+ * If there are index clauses, try to evaluate the filter on the index
+ * tuple first, and only when it fails try the index tuple.
+ *
+ * https://www.postgresql.org/message-id/N1xaIrU29uk5YxLyW55MGk5fz9s6V2FNtj54JRaVlFbPixD5z8sJ07Ite5CvbWwik8ZvDG07oSTN-usENLVMq2UAcizVTEd5b-o16ZGDIIU%3D%40yamlcoder.me
+ */
+ if (node->indexfilters != NULL)
+ {
+ ItemPointer tid = &scandesc->xs_heaptid;
+
+ /*
+ * XXX see nodeIndexonlyscan.c, but inverse - we only do this when
+ * we can check some filters on the index tuple.
+ */
+ if (VM_ALL_VISIBLE(scandesc->heapRelation,
+ ItemPointerGetBlockNumber(tid),
+ &node->iss_VMBuffer))
+ {
+ /*
+ * Only MVCC snapshots are supported here, so there should be no
+ * need to keep following the HOT chain once a visible entry has
+ * been found. If we did want to allow that, we'd need to keep
+ * more state to remember not to call index_getnext_tid next time.
+ *
+ * XXX Is there a place where we can decide whether to consider
+ * this optimization, based on which type of snapshot we're going
+ * to use? And disable it for non-MVCC ones? That'd mean this
+ * error can't really happen here. Or how can we even get this
+ * error now?
+ */
+ if (scandesc->xs_heap_continue)
+ elog(ERROR, "non-MVCC snapshots are not supported with index filters");
+
+ /*
+ * Fill the scan tuple slot with data from the index. This might be
+ * provided in either HeapTuple or IndexTuple format. Conceivably an
+ * index AM might fill both fields, in which case we prefer the heap
+ * format, since it's probably a bit cheaper to fill a slot from.
+ */
+ if (scandesc->xs_hitup)
+ {
+ StoreHeapTuple(node->iss_TableSlot, scandesc->xs_hitup,
+ scandesc->xs_hitupdesc, node->iss_IndexInfo);
+ }
+ else if (scandesc->xs_itup)
+ StoreIndexTuple(node->iss_TableSlot, scandesc->xs_itup,
+ scandesc->xs_itupdesc, node->iss_IndexInfo);
+ else
+ elog(ERROR, "no data returned for index-only scan");
+
+ /* run the expressions
+ *
+ * XXX This does not work, because for some indexes the index key type
+ * may differ from the table attribute type. This happens when the
+ * (pg_opclass.opcintype != opckeytype). There's about 10 such built-in
+ * opclasses, e.g. name_ops creates cstring on name columns. A good
+ * example is pg_namespace.nspname, with the index on nspname causing
+ * trouble for \dT.
+ *
+ * One option would be to tweak the type for the Var, but I'm not sure
+ * that's actually correct. Surely some of the types may not be binary
+ * compatible (e.g. jsonb_ops has opcintype=jsonb and opckeytype=text).
+ * So maybe we should just disable index-only filters for such cases.
+ */
+ econtext->ecxt_scantuple = node->iss_TableSlot;
+
+ /* check the filters pushed to the index-tuple level */
+ if (!ExecQual(node->indexfilters, econtext))
+ {
+ InstrCountFiltered2(node, 1);
+ continue;
+ }
+
+ /* remember we already checked the filter */
+ filter_checked = true;
+ }
+ }
+
+ /*
+ * Fetch the next (or only) visible heap tuple for this index entry.
+ * If we don't find anything, loop around and grab the next TID from
+ * the index.
+ */
+ if (index_fetch_heap(scandesc, slot))
+ {
+ has_index_tuple = true;
+ break;
+ }
+ }
+
+ if (!has_index_tuple)
+ break;
+
+ /*
+ * If we didn't manage to check the filter on the index tuple (because
+ * of the page not being all-visible, etc.), do that now on the heap
+ * tuple.
+ */
+ if (!filter_checked)
+ {
+ econtext->ecxt_scantuple = slot;
+ if (!ExecQual(node->indexfiltersorig, econtext))
+ {
+ InstrCountFiltered2(node, 1);
+ continue;
+ }
+ }
+
/*
* If the index was lossy, we have to recheck the index quals using
* the fetched tuple.
@@ -794,6 +946,13 @@ ExecEndIndexScan(IndexScanState *node)
indexRelationDesc = node->iss_RelationDesc;
indexScanDesc = node->iss_ScanDesc;
+ /* Release VM buffer pin, if any. */
+ if (node->iss_VMBuffer != InvalidBuffer)
+ {
+ ReleaseBuffer(node->iss_VMBuffer);
+ node->iss_VMBuffer = InvalidBuffer;
+ }
+
/*
* Free the exprcontext(s) ... now dead code, see ExecFreeExprContext
*/
@@ -956,6 +1115,10 @@ ExecInitIndexScan(IndexScan *node, EState *estate, int eflags)
ExecInitQual(node->scan.plan.qual, (PlanState *) indexstate);
indexstate->indexqualorig =
ExecInitQual(node->indexqualorig, (PlanState *) indexstate);
+ indexstate->indexfilters =
+ ExecInitQual(node->indexfilters, (PlanState *) indexstate);
+ indexstate->indexfiltersorig =
+ ExecInitQual(node->indexfiltersorig, (PlanState *) indexstate);
indexstate->indexorderbyorig =
ExecInitExprList(node->indexorderbyorig, (PlanState *) indexstate);
@@ -971,6 +1134,19 @@ ExecInitIndexScan(IndexScan *node, EState *estate, int eflags)
lockmode = exec_rt_fetch(node->scan.scanrelid, estate)->rellockmode;
indexstate->iss_RelationDesc = index_open(node->indexid, lockmode);
+ /*
+ * We need another slot, in a format that's suitable for the table AM, for
+ * when we need to evaluate index-only filter on data from index tuple.
+ *
+ * XXX Maybe this could use the scan tuple slot?
+ */
+ indexstate->iss_TableSlot =
+ ExecAllocTableSlot(&estate->es_tupleTable,
+ RelationGetDescr(currentRelation),
+ table_slot_callbacks(currentRelation));
+
+ indexstate->iss_IndexInfo = BuildIndexInfo(indexstate->iss_RelationDesc);
+
/*
* Initialize index-specific scan state
*/
@@ -1744,3 +1920,83 @@ ExecIndexScanInitializeWorker(IndexScanState *node,
node->iss_ScanKeys, node->iss_NumScanKeys,
node->iss_OrderByKeys, node->iss_NumOrderByKeys);
}
+
+/*
+ * StoreIndexTuple
+ * Fill the slot with data from the index tuple.
+ *
+ * At some point this might be generally-useful functionality, but
+ * right now we don't need it elsewhere.
+ */
+static void
+StoreIndexTuple(TupleTableSlot *slot, IndexTuple itup, TupleDesc itupdesc, IndexInfo *iinfo)
+{
+ bool *isnull;
+ Datum *values;
+
+ isnull = palloc0(itupdesc->natts * sizeof(bool));
+ values = palloc0(itupdesc->natts * sizeof(Datum));
+
+ /* expand the index tuple */
+ index_deform_tuple(itup, itupdesc, values, isnull);
+
+ /* now fill the values from the index tuple into the table slot */
+ ExecClearTuple(slot);
+
+ for (int i = 0; i < iinfo->ii_NumIndexAttrs; i++)
+ {
+ AttrNumber attnum = iinfo->ii_IndexAttrNumbers[i];
+
+ /* skip expressions */
+ if (attnum > 0)
+ {
+ slot->tts_isnull[attnum - 1] = isnull[i];
+ slot->tts_values[attnum - 1] = values[i];
+ }
+ }
+
+ pfree(values);
+ pfree(isnull);
+
+ ExecStoreVirtualTuple(slot);
+}
+
+/*
+ * StoreHeapTuple
+ * Fill the slot with data from the heap tuple (from the index).
+ *
+ * At some point this might be generally-useful functionality, but
+ * right now we don't need it elsewhere.
+ */
+static void
+StoreHeapTuple(TupleTableSlot *slot, HeapTuple tup, TupleDesc tupdesc, IndexInfo *iinfo)
+{
+ bool *isnull;
+ Datum *values;
+
+ isnull = palloc0(tupdesc->natts * sizeof(bool));
+ values = palloc0(tupdesc->natts * sizeof(Datum));
+
+ /* expand the index tuple */
+ heap_deform_tuple(tup, tupdesc, values, isnull);
+
+ /* now fill the values from the index tuple into the table slot */
+ ExecClearTuple(slot);
+
+ for (int i = 0; i < iinfo->ii_NumIndexAttrs; i++)
+ {
+ AttrNumber attnum = iinfo->ii_IndexAttrNumbers[i];
+
+ /* skip expressions */
+ if (attnum > 0)
+ {
+ slot->tts_isnull[attnum - 1] = isnull[i];
+ slot->tts_values[attnum - 1] = values[i];
+ }
+ }
+
+ pfree(values);
+ pfree(isnull);
+
+ ExecStoreVirtualTuple(slot);
+}
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index ef475d95a18..ef4be8d6bb3 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -135,6 +135,7 @@ int max_parallel_workers_per_gather = 2;
bool enable_seqscan = true;
bool enable_indexscan = true;
bool enable_indexonlyscan = true;
+bool enable_indexonlyfilter = true;
bool enable_bitmapscan = true;
bool enable_tidscan = true;
bool enable_sort = true;
@@ -607,6 +608,28 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count,
/* estimate number of main-table tuples fetched */
tuples_fetched = clamp_row_est(indexSelectivity * baserel->tuples);
+ /*
+ * If some filters can be evaluated on the index tuple, account for that.
+ * We need to scan all tuples from pages that are not all-visible, and
+ * for the remaining tuples we fetch those not eliminated by the filter.
+ *
+ * XXX Does this need to worry about path->path.param_info?
+ *
+ * XXX All of this seems overly manual / ad-hoc, surely there's a place
+ * where we already do this in a more elegant manner?
+ */
+ if (path->indexfilters != NIL)
+ {
+ Selectivity sel;
+
+ sel = clauselist_selectivity(root, path->indexfilters, baserel->relid,
+ JOIN_INNER, NULL);
+
+ tuples_fetched *= (1.0 - baserel->allvisfrac) + (baserel->allvisfrac) * sel;
+
+ tuples_fetched = clamp_row_est(tuples_fetched);
+ }
+
/* fetch estimated page costs for tablespace containing table */
get_tablespace_page_costs(baserel->reltablespace,
&spc_random_page_cost,
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 6a93d767a5c..4dc5b814f00 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -101,9 +101,11 @@ static bool eclass_already_used(EquivalenceClass *parent_ec, Relids oldrelids,
List *indexjoinclauses);
static void get_index_paths(PlannerInfo *root, RelOptInfo *rel,
IndexOptInfo *index, IndexClauseSet *clauses,
- List **bitindexpaths);
+ List *filters, List **bitindexpaths);
static List *build_index_paths(PlannerInfo *root, RelOptInfo *rel,
- IndexOptInfo *index, IndexClauseSet *clauses,
+ IndexOptInfo *index,
+ IndexClauseSet *clauses,
+ List *filters,
bool useful_predicate,
ScanTypeControl scantype,
bool *skip_nonnative_saop,
@@ -124,6 +126,7 @@ static PathClauseUsage *classify_index_clause_usage(Path *path,
static void find_indexpath_quals(Path *bitmapqual, List **quals, List **preds);
static int find_list_position(Node *node, List **nodelist);
static bool check_index_only(RelOptInfo *rel, IndexOptInfo *index);
+static bool check_index_filter(RelOptInfo *rel, IndexOptInfo *index, Node *clause);
static double get_loop_count(PlannerInfo *root, Index cur_relid, Relids outer_relids);
static double adjust_rowcount_for_semijoins(PlannerInfo *root,
Index cur_relid,
@@ -132,7 +135,8 @@ static double adjust_rowcount_for_semijoins(PlannerInfo *root,
static double approximate_joinrel_size(PlannerInfo *root, Relids relids);
static void match_restriction_clauses_to_index(PlannerInfo *root,
IndexOptInfo *index,
- IndexClauseSet *clauseset);
+ IndexClauseSet *clauseset,
+ List **filters);
static void match_join_clauses_to_index(PlannerInfo *root,
RelOptInfo *rel, IndexOptInfo *index,
IndexClauseSet *clauseset,
@@ -143,15 +147,20 @@ static void match_eclass_clauses_to_index(PlannerInfo *root,
static void match_clauses_to_index(PlannerInfo *root,
List *clauses,
IndexOptInfo *index,
- IndexClauseSet *clauseset);
+ IndexClauseSet *clauseset,
+ List **filters);
static void match_clause_to_index(PlannerInfo *root,
RestrictInfo *rinfo,
IndexOptInfo *index,
- IndexClauseSet *clauseset);
+ IndexClauseSet *clauseset,
+ List **filters);
static IndexClause *match_clause_to_indexcol(PlannerInfo *root,
RestrictInfo *rinfo,
int indexcol,
IndexOptInfo *index);
+static RestrictInfo *match_filter_to_index(PlannerInfo *root,
+ RestrictInfo *rinfo,
+ IndexOptInfo *index);
static bool IsBooleanOpfamily(Oid opfamily);
static IndexClause *match_boolean_index_clause(PlannerInfo *root,
RestrictInfo *rinfo,
@@ -241,6 +250,7 @@ create_index_paths(PlannerInfo *root, RelOptInfo *rel)
IndexClauseSet rclauseset;
IndexClauseSet jclauseset;
IndexClauseSet eclauseset;
+ List *rfilters;
ListCell *lc;
/* Skip the whole mess if no indexes */
@@ -270,14 +280,15 @@ create_index_paths(PlannerInfo *root, RelOptInfo *rel)
* Identify the restriction clauses that can match the index.
*/
MemSet(&rclauseset, 0, sizeof(rclauseset));
- match_restriction_clauses_to_index(root, index, &rclauseset);
+ rfilters = NIL;
+ match_restriction_clauses_to_index(root, index, &rclauseset, &rfilters);
/*
* Build index paths from the restriction clauses. These will be
* non-parameterized paths. Plain paths go directly to add_path(),
* bitmap paths are added to bitindexpaths to be handled below.
*/
- get_index_paths(root, rel, index, &rclauseset,
+ get_index_paths(root, rel, index, &rclauseset, rfilters,
&bitindexpaths);
/*
@@ -301,6 +312,8 @@ create_index_paths(PlannerInfo *root, RelOptInfo *rel)
/*
* If we found any plain or eclass join clauses, build parameterized
* index paths using them.
+ *
+ * XXX Maybe pass the filters too?
*/
if (jclauseset.nonempty || eclauseset.nonempty)
consider_index_join_clauses(root, rel, index,
@@ -662,7 +675,7 @@ get_join_index_paths(PlannerInfo *root, RelOptInfo *rel,
Assert(clauseset.nonempty);
/* Build index path(s) using the collected set of clauses */
- get_index_paths(root, rel, index, &clauseset, bitindexpaths);
+ get_index_paths(root, rel, index, &clauseset, NULL, bitindexpaths);
/*
* Remember we considered paths for this set of relids.
@@ -712,7 +725,7 @@ eclass_already_used(EquivalenceClass *parent_ec, Relids oldrelids,
static void
get_index_paths(PlannerInfo *root, RelOptInfo *rel,
IndexOptInfo *index, IndexClauseSet *clauses,
- List **bitindexpaths)
+ List *filters, List **bitindexpaths)
{
List *indexpaths;
bool skip_nonnative_saop = false;
@@ -726,7 +739,7 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
* paths if possible).
*/
indexpaths = build_index_paths(root, rel,
- index, clauses,
+ index, clauses, filters,
index->predOK,
ST_ANYSCAN,
&skip_nonnative_saop,
@@ -741,7 +754,7 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
{
indexpaths = list_concat(indexpaths,
build_index_paths(root, rel,
- index, clauses,
+ index, clauses, filters,
index->predOK,
ST_ANYSCAN,
&skip_nonnative_saop,
@@ -781,7 +794,7 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
if (skip_nonnative_saop)
{
indexpaths = build_index_paths(root, rel,
- index, clauses,
+ index, clauses, filters,
false,
ST_BITMAPSCAN,
NULL,
@@ -833,7 +846,9 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
*/
static List *
build_index_paths(PlannerInfo *root, RelOptInfo *rel,
- IndexOptInfo *index, IndexClauseSet *clauses,
+ IndexOptInfo *index,
+ IndexClauseSet *clauses,
+ List *filters,
bool useful_predicate,
ScanTypeControl scantype,
bool *skip_nonnative_saop,
@@ -842,6 +857,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
List *result = NIL;
IndexPath *ipath;
List *index_clauses;
+ List *index_filters;
Relids outer_relids;
double loop_count;
List *orderbyclauses;
@@ -947,6 +963,26 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
return NIL;
}
+ /*
+ * If we have index-only filters, combine the clauses into a simple list and
+ * add the relids to outer relids.
+ */
+ index_filters = NIL;
+ if (filters)
+ {
+ ListCell *lc;
+
+ foreach(lc, filters)
+ {
+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
+
+ /* OK to include this clause (as a filter) */
+ index_filters = lappend(index_filters, rinfo);
+ outer_relids = bms_add_members(outer_relids,
+ rinfo->clause_relids);
+ }
+ }
+
/* We do not want the index's rel itself listed in outer_relids */
outer_relids = bms_del_member(outer_relids, rel->relid);
@@ -1015,6 +1051,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
{
ipath = create_index_path(root, index,
index_clauses,
+ index_filters,
orderbyclauses,
orderbyclausecols,
useful_pathkeys,
@@ -1035,6 +1072,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
{
ipath = create_index_path(root, index,
index_clauses,
+ index_filters,
orderbyclauses,
orderbyclausecols,
useful_pathkeys,
@@ -1068,6 +1106,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
{
ipath = create_index_path(root, index,
index_clauses,
+ index_filters,
NIL,
NIL,
useful_pathkeys,
@@ -1085,6 +1124,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
{
ipath = create_index_path(root, index,
index_clauses,
+ index_filters,
NIL,
NIL,
useful_pathkeys,
@@ -1147,6 +1187,7 @@ build_paths_for_OR(PlannerInfo *root, RelOptInfo *rel,
{
IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
IndexClauseSet clauseset;
+ List *filters;
List *indexpaths;
bool useful_predicate;
@@ -1191,11 +1232,14 @@ build_paths_for_OR(PlannerInfo *root, RelOptInfo *rel,
* Identify the restriction clauses that can match the index.
*/
MemSet(&clauseset, 0, sizeof(clauseset));
- match_clauses_to_index(root, clauses, index, &clauseset);
+ filters = NIL;
+ match_clauses_to_index(root, clauses, index, &clauseset, &filters);
/*
* If no matches so far, and the index predicate isn't useful, we
* don't want it.
+ *
+ * XXX Maybe this should check the filterset too?
*/
if (!clauseset.nonempty && !useful_predicate)
continue;
@@ -1203,13 +1247,13 @@ build_paths_for_OR(PlannerInfo *root, RelOptInfo *rel,
/*
* Add "other" restriction clauses to the clauseset.
*/
- match_clauses_to_index(root, other_clauses, index, &clauseset);
+ match_clauses_to_index(root, other_clauses, index, &clauseset, &filters);
/*
* Construct paths if possible.
*/
indexpaths = build_index_paths(root, rel,
- index, &clauseset,
+ index, &clauseset, filters,
useful_predicate,
ST_BITMAPSCAN,
NULL,
@@ -1853,6 +1897,62 @@ check_index_only(RelOptInfo *rel, IndexOptInfo *index)
return result;
}
+/*
+ * check_index_filter
+ * Determine whether a clause can be executed directly on the index tuple.
+ */
+static bool
+check_index_filter(RelOptInfo *rel, IndexOptInfo *index, Node *clause)
+{
+ bool result;
+ Bitmapset *attrs_used = NULL;
+ Bitmapset *index_canreturn_attrs = NULL;
+ int i;
+
+ /* Index-only scans must be enabled */
+ if (!enable_indexonlyfilter)
+ return false;
+
+ /*
+ * Check that all needed attributes of the relation are available from the
+ * index.
+ */
+
+ /*
+ * First, identify all the attributes needed by the clause.
+ */
+ pull_varattnos(clause, rel->relid, &attrs_used);
+
+ /*
+ * Construct a bitmapset of columns that the index can return back in an
+ * index-only scan.
+ */
+ for (i = 0; i < index->ncolumns; i++)
+ {
+ int attno = index->indexkeys[i];
+
+ /*
+ * For the moment, we just ignore index expressions. It might be nice
+ * to do something with them, later.
+ */
+ if (attno == 0)
+ continue;
+
+ if (index->canreturn[i])
+ index_canreturn_attrs =
+ bms_add_member(index_canreturn_attrs,
+ attno - FirstLowInvalidHeapAttributeNumber);
+ }
+
+ /* Do we have all the necessary attributes? */
+ result = bms_is_subset(attrs_used, index_canreturn_attrs);
+
+ bms_free(attrs_used);
+ bms_free(index_canreturn_attrs);
+
+ return result;
+}
+
/*
* get_loop_count
* Choose the loop count estimate to use for costing a parameterized path
@@ -2021,10 +2121,11 @@ approximate_joinrel_size(PlannerInfo *root, Relids relids)
static void
match_restriction_clauses_to_index(PlannerInfo *root,
IndexOptInfo *index,
- IndexClauseSet *clauseset)
+ IndexClauseSet *clauseset,
+ List **filters)
{
/* We can ignore clauses that are implied by the index predicate */
- match_clauses_to_index(root, index->indrestrictinfo, index, clauseset);
+ match_clauses_to_index(root, index->indrestrictinfo, index, clauseset, filters);
}
/*
@@ -2032,6 +2133,8 @@ match_restriction_clauses_to_index(PlannerInfo *root,
* Identify join clauses for the rel that match the index.
* Matching clauses are added to *clauseset.
* Also, add any potentially usable join OR clauses to *joinorclauses.
+ *
+ * FIXME Maybe this should fill the filterset too?
*/
static void
match_join_clauses_to_index(PlannerInfo *root,
@@ -2054,7 +2157,7 @@ match_join_clauses_to_index(PlannerInfo *root,
if (restriction_is_or_clause(rinfo))
*joinorclauses = lappend(*joinorclauses, rinfo);
else
- match_clause_to_index(root, rinfo, index, clauseset);
+ match_clause_to_index(root, rinfo, index, clauseset, NULL);
}
}
@@ -2062,6 +2165,8 @@ match_join_clauses_to_index(PlannerInfo *root,
* match_eclass_clauses_to_index
* Identify EquivalenceClass join clauses for the rel that match the index.
* Matching clauses are added to *clauseset.
+ *
+ * XXX Maybe this should fill the filterset too?
*/
static void
match_eclass_clauses_to_index(PlannerInfo *root, IndexOptInfo *index,
@@ -2092,7 +2197,7 @@ match_eclass_clauses_to_index(PlannerInfo *root, IndexOptInfo *index,
* since for non-btree indexes the EC's equality operators might not
* be in the index opclass (cf ec_member_matches_indexcol).
*/
- match_clauses_to_index(root, clauses, index, clauseset);
+ match_clauses_to_index(root, clauses, index, clauseset, NULL);
}
}
@@ -2105,7 +2210,8 @@ static void
match_clauses_to_index(PlannerInfo *root,
List *clauses,
IndexOptInfo *index,
- IndexClauseSet *clauseset)
+ IndexClauseSet *clauseset,
+ List **filters)
{
ListCell *lc;
@@ -2113,7 +2219,7 @@ match_clauses_to_index(PlannerInfo *root,
{
RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
- match_clause_to_index(root, rinfo, index, clauseset);
+ match_clause_to_index(root, rinfo, index, clauseset, filters);
}
}
@@ -2138,7 +2244,8 @@ static void
match_clause_to_index(PlannerInfo *root,
RestrictInfo *rinfo,
IndexOptInfo *index,
- IndexClauseSet *clauseset)
+ IndexClauseSet *clauseset,
+ List **filters)
{
int indexcol;
@@ -2187,6 +2294,20 @@ match_clause_to_index(PlannerInfo *root,
return;
}
}
+
+ /* if filterset is NULL, we're done */
+ if (!filters)
+ return;
+
+ /*
+ * We didn't record the clause as a regular index clause, so see if
+ * we can evaluate it as an index filter.
+ */
+ if ((rinfo = match_filter_to_index(root, rinfo, index)) != NULL)
+ {
+ /* FIXME maybe check/prevent duplicates, like above? */
+ *filters = lappend(*filters, rinfo);
+ }
}
/*
@@ -2322,6 +2443,29 @@ match_clause_to_indexcol(PlannerInfo *root,
return NULL;
}
+static RestrictInfo *
+match_filter_to_index(PlannerInfo *root,
+ RestrictInfo *rinfo,
+ IndexOptInfo *index)
+{
+ Expr *clause = rinfo->clause;
+
+ /*
+ * Historically this code has coped with NULL clauses. That's probably
+ * not possible anymore, but we might as well continue to cope.
+ */
+ if (clause == NULL)
+ return NULL;
+
+ /*
+ * Can the clause be evaluated only using the index tuple?
+ */
+ if (!check_index_filter(index->rel, index, (Node *) rinfo->clause))
+ return NULL;
+
+ return rinfo;
+}
+
/*
* IsBooleanOpfamily
* Detect whether an opfamily supports boolean equality as an operator.
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index af48109058d..9d436730961 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -166,10 +166,16 @@ static Node *replace_nestloop_params_mutator(Node *node, PlannerInfo *root);
static void fix_indexqual_references(PlannerInfo *root, IndexPath *index_path,
List **stripped_indexquals_p,
List **fixed_indexquals_p);
+static void fix_indexfilter_references(PlannerInfo *root, IndexPath *index_path,
+ List **stripped_indexfilters_p,
+ List **fixed_indexfilters_p);
static List *fix_indexorderby_references(PlannerInfo *root, IndexPath *index_path);
static Node *fix_indexqual_clause(PlannerInfo *root,
IndexOptInfo *index, int indexcol,
Node *clause, List *indexcolnos);
+static Node *fix_indexfilter_clause(PlannerInfo *root,
+ IndexOptInfo *index,
+ Node *clause);
static Node *fix_indexqual_operand(Node *node, IndexOptInfo *index, int indexcol);
static List *get_switched_clauses(List *clauses, Relids outerrelids);
static List *order_qual_clauses(PlannerInfo *root, List *clauses);
@@ -182,6 +188,7 @@ static SampleScan *make_samplescan(List *qptlist, List *qpqual, Index scanrelid,
TableSampleClause *tsc);
static IndexScan *make_indexscan(List *qptlist, List *qpqual, Index scanrelid,
Oid indexid, List *indexqual, List *indexqualorig,
+ List *indexfilters, List *indexfiltersorig,
List *indexorderby, List *indexorderbyorig,
List *indexorderbyops,
ScanDirection indexscandir);
@@ -2994,7 +3001,9 @@ create_indexscan_plan(PlannerInfo *root,
Oid indexoid = indexinfo->indexoid;
List *qpqual;
List *stripped_indexquals;
+ List *stripped_indexfilters;
List *fixed_indexquals;
+ List *fixed_indexfilters;
List *fixed_indexorderbys;
List *indexorderbyops = NIL;
ListCell *l;
@@ -3016,6 +3025,16 @@ create_indexscan_plan(PlannerInfo *root,
&stripped_indexquals,
&fixed_indexquals);
+ /*
+ * Extract the index qual expressions (stripped of RestrictInfos) from the
+ * IndexClauses list, and prepare a copy with index Vars substituted for
+ * table Vars. (This step also does replace_nestloop_params on the
+ * fixed_indexquals.)
+ */
+ fix_indexfilter_references(root, best_path,
+ &stripped_indexfilters,
+ &fixed_indexfilters);
+
/*
* Likewise fix up index attr references in the ORDER BY expressions.
*/
@@ -3084,6 +3103,8 @@ create_indexscan_plan(PlannerInfo *root,
{
stripped_indexquals = (List *)
replace_nestloop_params(root, (Node *) stripped_indexquals);
+ stripped_indexfilters = (List *)
+ replace_nestloop_params(root, (Node *) stripped_indexfilters);
qpqual = (List *)
replace_nestloop_params(root, (Node *) qpqual);
indexorderbys = (List *)
@@ -3160,6 +3181,8 @@ create_indexscan_plan(PlannerInfo *root,
indexoid,
fixed_indexquals,
stripped_indexquals,
+ fixed_indexfilters,
+ stripped_indexfilters,
fixed_indexorderbys,
indexorderbys,
indexorderbyops,
@@ -5004,6 +5027,40 @@ fix_indexqual_references(PlannerInfo *root, IndexPath *index_path,
*fixed_indexquals_p = fixed_indexquals;
}
+/*
+ * fix_indexfilter_references
+ * Adjust indexfilter clauses to the form the executor's indexfilter
+ * machinery needs.
+ *
+ * XXX This does similar stuff to fix_indexqual_references does, except that it
+ * doesn't switch the Vars to point to the index attnum (we'll expand the index
+ * tuple into the heap tuple and run the expression on that).
+ */
+static void
+fix_indexfilter_references(PlannerInfo *root, IndexPath *index_path,
+ List **stripped_indexfilters_p, List **fixed_indexfilters_p)
+{
+ IndexOptInfo *index = index_path->indexinfo;
+ List *stripped_indexfilters;
+ List *fixed_indexfilters;
+ ListCell *lc;
+
+ stripped_indexfilters = fixed_indexfilters = NIL;
+
+ foreach(lc, index_path->indexfilters)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+ Node *clause = (Node *) rinfo->clause;
+
+ stripped_indexfilters = lappend(stripped_indexfilters, clause);
+ clause = fix_indexfilter_clause(root, index, clause);
+ fixed_indexfilters = lappend(fixed_indexfilters, clause);
+ }
+
+ *stripped_indexfilters_p = stripped_indexfilters;
+ *fixed_indexfilters_p = fixed_indexfilters;
+}
+
/*
* fix_indexorderby_references
* Adjust indexorderby clauses to the form the executor's index
@@ -5102,6 +5159,25 @@ fix_indexqual_clause(PlannerInfo *root, IndexOptInfo *index, int indexcol,
return clause;
}
+/*
+ * fix_indexfilter_clause
+ * Convert a single indexqual clause to the form needed by the executor.
+ *
+ * We only replace nestloop params here. The Vars are left pointing to the
+ * table varno.
+ */
+static Node *
+fix_indexfilter_clause(PlannerInfo *root, IndexOptInfo *index, Node *clause)
+{
+ /*
+ * Replace any outer-relation variables with nestloop params.
+ *
+ * This also makes a copy of the clause, so it's safe to modify it
+ * in-place below (not done, actually).
+ */
+ return replace_nestloop_params(root, clause);
+}
+
/*
* fix_indexqual_operand
* Convert an indexqual expression to a Var referencing the index column.
@@ -5500,6 +5576,8 @@ make_indexscan(List *qptlist,
Oid indexid,
List *indexqual,
List *indexqualorig,
+ List *indexfilters,
+ List *indexfiltersorig,
List *indexorderby,
List *indexorderbyorig,
List *indexorderbyops,
@@ -5516,6 +5594,8 @@ make_indexscan(List *qptlist,
node->indexid = indexid;
node->indexqual = indexqual;
node->indexqualorig = indexqualorig;
+ node->indexfilters = indexfilters;
+ node->indexfiltersorig = indexfiltersorig;
node->indexorderby = indexorderby;
node->indexorderbyorig = indexorderbyorig;
node->indexorderbyops = indexorderbyops;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 44efb1f4ebc..5c2840201da 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -6661,7 +6661,7 @@ plan_cluster_use_sort(Oid tableOid, Oid indexOid)
/* Estimate the cost of index scan */
indexScanPath = create_index_path(root, indexInfo,
- NIL, NIL, NIL, NIL,
+ NIL, NIL, NIL, NIL, NIL,
ForwardScanDirection, false,
NULL, 1.0, false);
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 97fa561e4ea..3e2d0c60c53 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -667,6 +667,12 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
splan->indexqualorig =
fix_scan_list(root, splan->indexqualorig,
rtoffset, NUM_EXEC_QUAL(plan));
+ splan->indexfilters =
+ fix_scan_list(root, splan->indexfilters,
+ rtoffset, 1);
+ splan->indexfiltersorig =
+ fix_scan_list(root, splan->indexfiltersorig,
+ rtoffset, NUM_EXEC_QUAL(plan));
splan->indexorderby =
fix_scan_list(root, splan->indexorderby,
rtoffset, 1);
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index f123fcb41e3..3ae8c84bd29 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -995,6 +995,7 @@ IndexPath *
create_index_path(PlannerInfo *root,
IndexOptInfo *index,
List *indexclauses,
+ List *indexfilters,
List *indexorderbys,
List *indexorderbycols,
List *pathkeys,
@@ -1019,6 +1020,7 @@ create_index_path(PlannerInfo *root,
pathnode->indexinfo = index;
pathnode->indexclauses = indexclauses;
+ pathnode->indexfilters = indexfilters;
pathnode->indexorderbys = indexorderbys;
pathnode->indexorderbycols = indexorderbycols;
pathnode->indexscandir = indexscandir;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 93dc2e76809..a2ee529dc71 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -838,6 +838,16 @@ struct config_bool ConfigureNamesBool[] =
true,
NULL, NULL, NULL
},
+ {
+ {"enable_indexonlyfilter", PGC_USERSET, QUERY_TUNING_METHOD,
+ gettext_noop("Enables the planner's use of index to evaluate filters."),
+ NULL,
+ GUC_EXPLAIN
+ },
+ &enable_indexonlyfilter,
+ true,
+ NULL, NULL, NULL
+ },
{
{"enable_bitmapscan", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Enables the planner's use of bitmap-scan plans."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index e4c0269fa3d..c874ec9c3c2 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -381,6 +381,7 @@
#enable_incremental_sort = on
#enable_indexscan = on
#enable_indexonlyscan = on
+#enable_indexonlyfilter = on
#enable_material = on
#enable_memoize = on
#enable_mergejoin = on
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index cb714f4a196..98ff183636c 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1572,6 +1572,13 @@ typedef struct IndexScanState
Relation iss_RelationDesc;
struct IndexScanDescData *iss_ScanDesc;
+ /* index-only filters */
+ ExprState *indexfilters;
+ ExprState *indexfiltersorig;
+ TupleTableSlot *iss_TableSlot;
+ Buffer iss_VMBuffer;
+ IndexInfo *iss_IndexInfo;
+
/* These are needed for re-checking ORDER BY expr ordering */
pairingheap *iss_ReorderQueue;
bool iss_ReachedEnd;
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index c17b53f7adb..5c856b41efb 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1677,6 +1677,7 @@ typedef struct IndexPath
Path path;
IndexOptInfo *indexinfo;
List *indexclauses;
+ List *indexfilters;
List *indexorderbys;
List *indexorderbycols;
ScanDirection indexscandir;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 1b787fe0318..cf02b939b23 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -450,6 +450,8 @@ typedef struct IndexScan
Oid indexid; /* OID of index to scan */
List *indexqual; /* list of index quals (usually OpExprs) */
List *indexqualorig; /* the same in original form */
+ List *indexfilters; /* quals for included columns */
+ List *indexfiltersorig; /* the same in original form */
List *indexorderby; /* list of index ORDER BY exprs */
List *indexorderbyorig; /* the same in original form */
List *indexorderbyops; /* OIDs of sort ops for ORDER BY exprs */
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 6cf49705d3a..02ed1212715 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -52,6 +52,7 @@ extern PGDLLIMPORT int max_parallel_workers_per_gather;
extern PGDLLIMPORT bool enable_seqscan;
extern PGDLLIMPORT bool enable_indexscan;
extern PGDLLIMPORT bool enable_indexonlyscan;
+extern PGDLLIMPORT bool enable_indexonlyfilter;
extern PGDLLIMPORT bool enable_bitmapscan;
extern PGDLLIMPORT bool enable_tidscan;
extern PGDLLIMPORT bool enable_sort;
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 001e75b5b76..d5e380c8275 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -41,6 +41,7 @@ extern Path *create_samplescan_path(PlannerInfo *root, RelOptInfo *rel,
extern IndexPath *create_index_path(PlannerInfo *root,
IndexOptInfo *index,
List *indexclauses,
+ List *indexfilters,
List *indexorderbys,
List *indexorderbycols,
List *pathkeys,
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index acfd9d1f4f7..448f26004e4 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1838,18 +1838,13 @@ DROP TABLE onek_with_null;
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on tenk1
- Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42)))
- -> BitmapOr
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 1))
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 3))
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 42))
-(9 rows)
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: (thousand = 42)
+ Index Filter: ((tenthous = 1) OR (tenthous = 3) OR (tenthous = 42))
+ Filter: ((tenthous = 1) OR (tenthous = 3) OR (tenthous = 42))
+(4 rows)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 001c6e7eb9d..b641fa7fd8d 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -117,6 +117,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_hashagg | on
enable_hashjoin | on
enable_incremental_sort | on
+ enable_indexonlyfilter | on
enable_indexonlyscan | on
enable_indexscan | on
enable_material | on
@@ -132,7 +133,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(21 rows)
+(22 rows)
-- Test that the pg_timezone_names and pg_timezone_abbrevs views are
-- more-or-less working. We can't test their contents in any great detail
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 1950e6f281f..37c28b29791 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -2599,6 +2599,7 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-> Index Scan using t1_a_idx on public.t1 t1_1
Output: t1_1.tableoid, t1_1.ctid
Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7))
+ Index Filter: ((SubPlan 1) AND leakproof(t1_1.a) AND (t1_1.a <> 6))
Filter: ((t1_1.a <> 6) AND (SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
SubPlan 1
-> Append
@@ -2609,16 +2610,19 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-> Index Scan using t11_a_idx on public.t11 t1_2
Output: t1_2.tableoid, t1_2.ctid
Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7))
+ Index Filter: ((SubPlan 1) AND leakproof(t1_2.a) AND (t1_2.a <> 6))
Filter: ((t1_2.a <> 6) AND (SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t12_a_idx on public.t12 t1_3
Output: t1_3.tableoid, t1_3.ctid
Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7))
+ Index Filter: ((SubPlan 1) AND leakproof(t1_3.a) AND (t1_3.a <> 6))
Filter: ((t1_3.a <> 6) AND (SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-> Index Scan using t111_a_idx on public.t111 t1_4
Output: t1_4.tableoid, t1_4.ctid
Index Cond: ((t1_4.a > 5) AND (t1_4.a < 7))
+ Index Filter: ((SubPlan 1) AND leakproof(t1_4.a) AND (t1_4.a <> 6))
Filter: ((t1_4.a <> 6) AND (SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
-(30 rows)
+(34 rows)
UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100
@@ -2646,6 +2650,7 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-> Index Scan using t1_a_idx on public.t1 t1_1
Output: t1_1.a, t1_1.tableoid, t1_1.ctid
Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8))
+ Index Filter: ((SubPlan 1) AND leakproof(t1_1.a))
Filter: ((SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
SubPlan 1
-> Append
@@ -2656,16 +2661,19 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-> Index Scan using t11_a_idx on public.t11 t1_2
Output: t1_2.a, t1_2.tableoid, t1_2.ctid
Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8))
+ Index Filter: ((SubPlan 1) AND leakproof(t1_2.a))
Filter: ((SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t12_a_idx on public.t12 t1_3
Output: t1_3.a, t1_3.tableoid, t1_3.ctid
Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8))
+ Index Filter: ((SubPlan 1) AND leakproof(t1_3.a))
Filter: ((SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-> Index Scan using t111_a_idx on public.t111 t1_4
Output: t1_4.a, t1_4.tableoid, t1_4.ctid
Index Cond: ((t1_4.a > 5) AND (t1_4.a = 8))
+ Index Filter: ((SubPlan 1) AND leakproof(t1_4.a))
Filter: ((SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
-(30 rows)
+(34 rows)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
NOTICE: snooped value: 8
--
2.41.0
