public inbox for [email protected]
help / color / mirror / Atom feedFrom: =?utf-8?B?Wml6aHVhbkxpdSBYLU1BTg==?= <[email protected]>
To: =?utf-8?B?amlhbiBoZQ==?= <[email protected]>
To: =?utf-8?B?UGV0ZXIgRWlzZW50cmF1dA==?= <[email protected]>
To: =?utf-8?B?cGdzcWwtaGFja2Vycw==?= <[email protected]>
Cc: =?utf-8?B?Q29yZXkgSHVpbmtlcg==?= <[email protected]>
Cc: =?utf-8?B?VG9tIExhbmU=?= <[email protected]>
Cc: =?utf-8?B?Y2hlbmdwZW5nX3lhbg==?= <[email protected]>
Cc: =?utf-8?B?Z3VvZmVuZ2xpbnV4?= <[email protected]>
Cc: =?utf-8?B?5oiR6Ieq5bex55qE6YKu566x?= <[email protected]>
Subject: Re: support create index on virtual generated column.
Date: Wed, 8 Jul 2026 18:02:40 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CACJufxGao-cypdNhifHAdt8jHfK6-HX=tRBovBkgRuxw063GaA@mail.gmail.com>
<CALdSSPh1opqDbVSSrrWr40B-3TS+q8EfGRLqJRVw+j15X6nPDQ@mail.gmail.com>
<CACJufxGu3jjiyKqPRKP+n92-7u3S_yexCLe1NCQywe96jQxzrg@mail.gmail.com>
<CALdSSPiMrRhSyQvAqGxgLMdcmFLrXSDAsCt32pg=e2bh2k4_Tg@mail.gmail.com>
<CACJufxFx2zmTnTrGaxfNpRpy3RbY9wpjBXqvfP5dWoxsW9f_FQ@mail.gmail.com>
<CACJufxGgkH0PyyqP6ggqcEWHxZzmkV=puY8ad=s8kisss9MAwg@mail.gmail.com>
<CACJufxFBJa2_0jm7q7ZhS5Y=ZTU_b+5YHdQ6vK4Dp8xChUj3Ww@mail.gmail.com>
<CADkLM=dtJZTrbUdFq5UznRepSoYpB2ARm4Q6yGvcgPrxwTtMrQ@mail.gmail.com>
<[email protected]>
<CADkLM=c09mZi_XFix5EyHXPz1Z0k7=-DBV+iAOicCrDiqCqFrw@mail.gmail.com>
<CACJufxEygt-OkS9qSCshNu+fq4533AA0BCBtboW9Mi3xujp=Uw@mail.gmail.com>
<[email protected]>
<CACJufxGhTjcc-z1=ejgtXCk7L3iy+B8aoDAvczaszDM4NPpDVw@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
Hi everyone,
CFBot reported V4 patch mismatched results for CREATE INDEX compared with previous runs.
I’ve conducted tests and revised the relevant code accordingly:
1.Modified genericcostestimate() in src/backend/utils/adt/selfuncs.c to calculate indexSelectivity
based on the estimated numIndexTuples.
2.Updated cost_index() located in src/backend/optimizer/path/costsize.c: when index->predOK
evaluates to true, temporarily assign baserel->rows to index->tuples, and restore the original value
before exiting the function.
I have carried out corresponding test cases. After these changes, PostgreSQL can properly rebuild indexes
and correctly compute the rows and column data that the rebuilt index should contain based on the new
expressions when executing related statements:
```SQL
xman5=# \d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+------------------------------------+---------+-------------+--------------+-------------
a | integer | | | | plain | | |
s1 | integer | | | generated always as (a * 1) stored | plain | | |
s2 | integer | | | generated always as (a * 2) stored | plain | | |
s3 | integer | | | generated always as (a * 3) stored | plain | | |
s4 | integer | | | generated always as (a * 4) stored | plain | | |
v1 | integer | | | generated always as (a * 11) | plain | | |
v2 | integer | | | generated always as (a * 22) | plain | | |
v3 | integer | | | generated always as (a * 33) | plain | | |
v4 | integer | | | generated always as (a * 44) | plain | | |
Indexes:
"idx_t1_s" btree (a, s1, abs(s2)) INCLUDE (s3) WHERE s4 < 100
"idx_t1_v" btree (a, v1, abs(v2)) INCLUDE (v3) WHERE v4 < 100
Access method: heap
update t1 set a = -2;
update t1 set a = -1;
ALTER TABLE t1 ALTER COLUMN v1 SET EXPRESSION AS (a * 110);
ALTER TABLE t1 ALTER COLUMN v2 SET EXPRESSION AS (a * 220);
ALTER TABLE t1 ALTER COLUMN v3 SET EXPRESSION AS (a * -330);
ALTER TABLE t1 ALTER COLUMN v4 SET EXPRESSION AS (a * -440);
```SQL
regards,
--
ZizhuanLiu (X-MAN)
[email protected]
Attachments:
[application/octet-stream] v5-0001-v5-enhance-index-support-for-virtual-generated-co.patch (54.0K, ../[email protected]/2-v5-0001-v5-enhance-index-support-for-virtual-generated-co.patch)
download | inline diff:
From b4185004901cae552ca15d1b60849453124c3e08 Mon Sep 17 00:00:00 2001
From: Zizhuan Liu <[email protected]>
Date: Wed, 8 Jul 2026 17:43:01 +0800
Subject: [PATCH v5] v5 enhance-index-support-for-virtual-generated-columns
---
src/backend/access/heap/heapam_handler.c | 20 +-
src/backend/bootstrap/bootstrap.c | 6 +
src/backend/catalog/index.c | 61 ++++-
src/backend/catalog/indexing.c | 2 +
src/backend/catalog/toasting.c | 4 +
src/backend/commands/analyze.c | 10 +-
src/backend/commands/indexcmds.c | 42 ++--
src/backend/executor/execIndexing.c | 13 +-
src/backend/nodes/makefuncs.c | 10 +-
src/backend/optimizer/path/costsize.c | 17 +-
src/backend/optimizer/path/indxpath.c | 22 +-
src/backend/optimizer/plan/createplan.c | 6 +-
src/backend/optimizer/util/plancat.c | 22 +-
src/backend/utils/adt/selfuncs.c | 25 +-
src/backend/utils/cache/relcache.c | 126 ++++++++++
src/include/nodes/execnodes.h | 4 +
src/include/nodes/pathnodes.h | 2 +
src/include/utils/rel.h | 2 +
src/include/utils/relcache.h | 4 +
.../regress/expected/generated_virtual.out | 234 +++++++++++++++---
src/test/regress/sql/generated_virtual.sql | 88 ++++---
21 files changed, 578 insertions(+), 142 deletions(-)
diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c
index 2268cc27..22e0762e 100644
--- a/src/backend/access/heap/heapam_handler.c
+++ b/src/backend/access/heap/heapam_handler.c
@@ -1159,7 +1159,7 @@ heapam_index_build_range_scan(Relation heapRelation,
Datum values[INDEX_MAX_KEYS];
bool isnull[INDEX_MAX_KEYS];
double reltuples;
- ExprState *predicate;
+ ExprState *predicateExpand;
TupleTableSlot *slot;
EState *estate;
ExprContext *econtext;
@@ -1200,7 +1200,7 @@ heapam_index_build_range_scan(Relation heapRelation,
econtext->ecxt_scantuple = slot;
/* Set up execution state for predicate, if any. */
- predicate = ExecPrepareQual(indexInfo->ii_Predicate, estate);
+ predicateExpand = ExecPrepareQual(indexInfo->ii_PredicateExpand, estate);
/*
* Prepare for scan of the base relation. In a normal index build, we use
@@ -1607,9 +1607,9 @@ heapam_index_build_range_scan(Relation heapRelation,
* In a partial index, discard tuples that don't satisfy the
* predicate.
*/
- if (predicate != NULL)
+ if (predicateExpand != NULL)
{
- if (!ExecQual(predicate, econtext))
+ if (!ExecQual(predicateExpand, econtext))
continue;
}
@@ -1709,7 +1709,9 @@ heapam_index_build_range_scan(Relation heapRelation,
/* These may have been pointing to the now-gone estate */
indexInfo->ii_ExpressionsState = NIL;
+ indexInfo->ii_ExpressionsExpandState = NIL;
indexInfo->ii_PredicateState = NULL;
+ indexInfo->ii_PredicateExpandState = NULL;
return reltuples;
}
@@ -1726,7 +1728,7 @@ heapam_index_validate_scan(Relation heapRelation,
HeapTuple heapTuple;
Datum values[INDEX_MAX_KEYS];
bool isnull[INDEX_MAX_KEYS];
- ExprState *predicate;
+ ExprState *predicateExpand;
TupleTableSlot *slot;
EState *estate;
ExprContext *econtext;
@@ -1758,7 +1760,7 @@ heapam_index_validate_scan(Relation heapRelation,
econtext->ecxt_scantuple = slot;
/* Set up execution state for predicate, if any. */
- predicate = ExecPrepareQual(indexInfo->ii_Predicate, estate);
+ predicateExpand = ExecPrepareQual(indexInfo->ii_PredicateExpand, estate);
/*
* Prepare for scan of the base relation. We need just those tuples
@@ -1895,9 +1897,9 @@ heapam_index_validate_scan(Relation heapRelation,
* In a partial index, discard tuples that don't satisfy the
* predicate.
*/
- if (predicate != NULL)
+ if (predicateExpand != NULL)
{
- if (!ExecQual(predicate, econtext))
+ if (!ExecQual(predicateExpand, econtext))
continue;
}
@@ -1952,7 +1954,9 @@ heapam_index_validate_scan(Relation heapRelation,
/* These may have been pointing to the now-gone estate */
indexInfo->ii_ExpressionsState = NIL;
+ indexInfo->ii_ExpressionsExpandState = NIL;
indexInfo->ii_PredicateState = NULL;
+ indexInfo->ii_PredicateExpandState = NULL;
}
/*
diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c
index b0dcd987..80bf4fc4 100644
--- a/src/backend/bootstrap/bootstrap.c
+++ b/src/backend/bootstrap/bootstrap.c
@@ -1156,11 +1156,17 @@ index_register(Oid heap,
/* expressions will likely be null, but may as well copy it */
newind->il_info->ii_Expressions =
copyObject(indexInfo->ii_Expressions);
+ newind->il_info->ii_ExpressionsExpand =
+ copyObject(indexInfo->ii_ExpressionsExpand);
newind->il_info->ii_ExpressionsState = NIL;
+ newind->il_info->ii_ExpressionsExpandState = NIL;
/* predicate will likely be null, but may as well copy it */
newind->il_info->ii_Predicate =
copyObject(indexInfo->ii_Predicate);
+ newind->il_info->ii_PredicateExpand =
+ copyObject(indexInfo->ii_PredicateExpand);
newind->il_info->ii_PredicateState = NULL;
+ newind->il_info->ii_PredicateExpandState = NULL;
/* no exclusion constraints at bootstrap time, so no need to copy */
Assert(indexInfo->ii_ExclusionOps == NULL);
Assert(indexInfo->ii_ExclusionProcs == NULL);
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 9407c357..ee260fde 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1403,6 +1403,10 @@ index_create_copy(Relation heapRelation, uint16 flags,
concurrently, /* concurrent */
indexRelation->rd_indam->amsummarizing,
oldInfo->ii_WithoutOverlaps);
+ newInfo->ii_ExpressionsExpand =
+ ExpandVirtualGeneratedColumns(newInfo->ii_ExpressionsExpand, heapRelation, InvalidOid);
+ newInfo->ii_PredicateExpand =
+ ExpandVirtualGeneratedColumns(newInfo->ii_PredicateExpand, heapRelation, InvalidOid);
/* fetch exclusion constraint info if any */
if (indexRelation->rd_index->indisexclusion)
@@ -2471,6 +2475,8 @@ BuildIndexInfo(Relation index)
false,
index->rd_indam->amsummarizing,
indexStruct->indisexclusion && indexStruct->indisunique);
+ ii->ii_ExpressionsExpand = (List *)RelationGetIndexExpressionsExpand(index);
+ ii->ii_PredicateExpand = (List *)RelationGetIndexPredicateExpand(index);
/* fill in attribute numbers */
for (i = 0; i < numAtts; i++)
@@ -2760,10 +2766,12 @@ FormIndexDatum(IndexInfo *indexInfo,
/* First time through, set up expression evaluation state */
indexInfo->ii_ExpressionsState =
ExecPrepareExprList(indexInfo->ii_Expressions, estate);
+ indexInfo->ii_ExpressionsExpandState =
+ ExecPrepareExprList(indexInfo->ii_ExpressionsExpand, estate);
/* Check caller has set up context correctly */
Assert(GetPerTupleExprContext(estate)->ecxt_scantuple == slot);
}
- indexpr_item = list_head(indexInfo->ii_ExpressionsState);
+ indexpr_item = list_head(indexInfo->ii_ExpressionsExpandState);
for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
{
@@ -2775,11 +2783,40 @@ FormIndexDatum(IndexInfo *indexInfo,
iDatum = slot_getsysattr(slot, keycol, &isNull);
else if (keycol != 0)
{
- /*
- * Plain index column; get the value we need directly from the
- * heap tuple.
- */
- iDatum = slot_getattr(slot, keycol, &isNull);
+ TupleDesc tupdesc = slot->tts_tupleDescriptor;
+ Form_pg_attribute att = TupleDescAttr(tupdesc, keycol - 1);
+
+ if (att->attgenerated != ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ /*
+ * Plain index column; get the value we need directly from the
+ * heap tuple.
+ */
+ iDatum = slot_getattr(slot, keycol, &isNull);
+ }
+ else
+ {
+ TupleConstr *constr = tupdesc->constr;
+
+ for (int j = 0; j < constr->num_defval; j++)
+ {
+ AttrDefault *defval;
+ Expr *expr = NULL;
+ ExprState *exprstate;
+
+ defval = &constr->defval[j];
+ if (defval->adnum == keycol)
+ {
+ expr = stringToNode(defval->adbin);
+ exprstate = ExecPrepareExpr(expr, estate);
+ iDatum = ExecEvalExprSwitchContext(exprstate,
+ GetPerTupleExprContext(estate),
+ &isNull);
+ break;
+ }
+ Assert(j + 1 < constr->num_defval);
+ }
+ }
}
else
{
@@ -2791,7 +2828,7 @@ FormIndexDatum(IndexInfo *indexInfo,
iDatum = ExecEvalExprSwitchContext((ExprState *) lfirst(indexpr_item),
GetPerTupleExprContext(estate),
&isNull);
- indexpr_item = lnext(indexInfo->ii_ExpressionsState, indexpr_item);
+ indexpr_item = lnext(indexInfo->ii_ExpressionsExpandState, indexpr_item);
}
values[i] = iDatum;
isnull[i] = isNull;
@@ -3220,7 +3257,7 @@ IndexCheckExclusion(Relation heapRelation,
TableScanDesc scan;
Datum values[INDEX_MAX_KEYS];
bool isnull[INDEX_MAX_KEYS];
- ExprState *predicate;
+ ExprState *predicateExpand;
TupleTableSlot *slot;
EState *estate;
ExprContext *econtext;
@@ -3246,7 +3283,7 @@ IndexCheckExclusion(Relation heapRelation,
econtext->ecxt_scantuple = slot;
/* Set up execution state for predicate, if any. */
- predicate = ExecPrepareQual(indexInfo->ii_Predicate, estate);
+ predicateExpand = ExecPrepareQual(indexInfo->ii_PredicateExpand, estate);
/*
* Scan all live tuples in the base relation.
@@ -3266,9 +3303,9 @@ IndexCheckExclusion(Relation heapRelation,
/*
* In a partial index, ignore tuples that don't satisfy the predicate.
*/
- if (predicate != NULL)
+ if (predicateExpand != NULL)
{
- if (!ExecQual(predicate, econtext))
+ if (!ExecQual(predicateExpand, econtext))
continue;
}
@@ -3301,7 +3338,9 @@ IndexCheckExclusion(Relation heapRelation,
/* These may have been pointing to the now-gone estate */
indexInfo->ii_ExpressionsState = NIL;
+ indexInfo->ii_ExpressionsExpandState = NIL;
indexInfo->ii_PredicateState = NULL;
+ indexInfo->ii_PredicateExpandState = NULL;
}
/*
diff --git a/src/backend/catalog/indexing.c b/src/backend/catalog/indexing.c
index fd7d2ec0..d27aaa09 100644
--- a/src/backend/catalog/indexing.c
+++ b/src/backend/catalog/indexing.c
@@ -133,7 +133,9 @@ CatalogIndexInsert(CatalogIndexState indstate, HeapTuple heapTuple,
* supported, nor exclusion constraints, nor deferred uniqueness
*/
Assert(indexInfo->ii_Expressions == NIL);
+ Assert(indexInfo->ii_ExpressionsExpand == NIL);
Assert(indexInfo->ii_Predicate == NIL);
+ Assert(indexInfo->ii_PredicateExpand == NIL);
Assert(indexInfo->ii_ExclusionOps == NULL);
Assert(index->rd_index->indimmediate);
Assert(indexInfo->ii_NumIndexKeyAttrs != 0);
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 4aa52a4b..daebfda1 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -298,9 +298,13 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
indexInfo->ii_IndexAttrNumbers[0] = 1;
indexInfo->ii_IndexAttrNumbers[1] = 2;
indexInfo->ii_Expressions = NIL;
+ indexInfo->ii_ExpressionsExpand = NIL;
indexInfo->ii_ExpressionsState = NIL;
+ indexInfo->ii_ExpressionsExpandState = NIL;
indexInfo->ii_Predicate = NIL;
+ indexInfo->ii_PredicateExpand = NIL;
indexInfo->ii_PredicateState = NULL;
+ indexInfo->ii_PredicateExpandState = NULL;
indexInfo->ii_ExclusionOps = NULL;
indexInfo->ii_ExclusionProcs = NULL;
indexInfo->ii_ExclusionStrats = NULL;
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index f66e80b7..fc67c24a 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -899,7 +899,7 @@ compute_index_stats(Relation onerel, double totalrows,
TupleTableSlot *slot;
EState *estate;
ExprContext *econtext;
- ExprState *predicate;
+ ExprState *predicateExpand;
Datum *exprvals;
bool *exprnulls;
int numindexrows,
@@ -908,7 +908,7 @@ compute_index_stats(Relation onerel, double totalrows,
double totalindexrows;
/* Ignore index if no columns to analyze and not partial */
- if (attr_cnt == 0 && indexInfo->ii_Predicate == NIL)
+ if (attr_cnt == 0 && indexInfo->ii_PredicateExpand == NIL)
continue;
/*
@@ -926,7 +926,7 @@ compute_index_stats(Relation onerel, double totalrows,
econtext->ecxt_scantuple = slot;
/* Set up execution state for predicate. */
- predicate = ExecPrepareQual(indexInfo->ii_Predicate, estate);
+ predicateExpand = ExecPrepareQual(indexInfo->ii_PredicateExpand, estate);
/* Compute and save index expression values */
exprvals = (Datum *) palloc(numrows * attr_cnt * sizeof(Datum));
@@ -949,9 +949,9 @@ compute_index_stats(Relation onerel, double totalrows,
ExecStoreHeapTuple(heapTuple, slot, false);
/* If index is partial, check predicate */
- if (predicate != NULL)
+ if (predicateExpand != NULL)
{
- if (!ExecQual(predicate, econtext))
+ if (!ExecQual(predicateExpand, econtext))
continue;
}
numindexrows++;
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 9ab74c8d..8a63efc8 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -932,6 +932,8 @@ DefineIndex(ParseState *pstate,
concurrent,
amissummarizing,
stmt->iswithoutoverlaps);
+ indexInfo->ii_PredicateExpand =
+ ExpandVirtualGeneratedColumns(indexInfo->ii_PredicateExpand, rel, InvalidOid);
typeIds = palloc_array(Oid, numberOfAttributes);
collationIds = palloc_array(Oid, numberOfAttributes);
@@ -948,6 +950,8 @@ DefineIndex(ParseState *pstate,
root_save_userid, root_save_sec_context,
&root_save_nestlevel);
+ indexInfo->ii_ExpressionsExpand =
+ ExpandVirtualGeneratedColumns(indexInfo->ii_ExpressionsExpand, rel, InvalidOid);
/*
* Extra checks when creating a PRIMARY KEY index.
*/
@@ -1110,7 +1114,7 @@ DefineIndex(ParseState *pstate,
* We disallow indexes on system columns. They would not necessarily get
* updated correctly, and they don't seem useful anyway.
*
- * Also disallow virtual generated columns in indexes (use expression
+ * Aisallow virtual generated columns in indexes (include using expression
* index instead).
*/
for (int i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
@@ -1123,7 +1127,7 @@ DefineIndex(ParseState *pstate,
errmsg("index creation on system columns is not supported")));
- if (attno > 0 &&
+ /*if (attno > 0 && i < numberOfKeyAttributes &&
TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
ereport(ERROR,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -1131,17 +1135,15 @@ DefineIndex(ParseState *pstate,
errmsg("primary keys on virtual generated columns are not supported") :
stmt->isconstraint ?
errmsg("unique constraints on virtual generated columns are not supported") :
- errmsg("indexes on virtual generated columns are not supported"));
+ errmsg("indexes on virtual generated columns are not supported"));*/
}
/*
- * Also check for system and generated columns used in expressions or
- * predicates.
+ * Also check for system columns used in expressions or predicates.
*/
if (indexInfo->ii_Expressions || indexInfo->ii_Predicate)
{
Bitmapset *indexattrs = NULL;
- int j;
pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &indexattrs);
pull_varattnos((Node *) indexInfo->ii_Predicate, 1, &indexattrs);
@@ -1154,25 +1156,6 @@ DefineIndex(ParseState *pstate,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("index creation on system columns is not supported")));
}
-
- /*
- * XXX Virtual generated columns in index expressions or predicates
- * could be supported, but it needs support in
- * RelationGetIndexExpressions() and RelationGetIndexPredicate().
- */
- j = -1;
- while ((j = bms_next_member(indexattrs, j)) >= 0)
- {
- AttrNumber attno = j + FirstLowInvalidHeapAttributeNumber;
-
- if (attno > 0 &&
- TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- stmt->isconstraint ?
- errmsg("unique constraints on virtual generated columns are not supported") :
- errmsg("indexes on virtual generated columns are not supported")));
- }
}
/* Is index safe for others to ignore? See set_indexsafe_procflags() */
@@ -1876,6 +1859,11 @@ CheckPredicate(Expr *predicate)
* If the caller switched to the table owner, ddl_userid is the role for ACL
* checks reached without traversing opaque expressions. Otherwise, it's
* InvalidOid, and other ddl_* arguments are undefined.
+ *
+ * Upon returning from this function, callers must apply
+ * ExpandVirtualGeneratedColumns() to ii_ExpressionsExpand
+ * when necessary for actual expansion if ii_ExpressionsExpand
+ * is not NIL or dummy.
*/
static void
ComputeIndexAttrs(ParseState *pstate,
@@ -2274,6 +2262,10 @@ ComputeIndexAttrs(ParseState *pstate,
attn++;
}
+
+ indexInfo->ii_ExpressionsExpand =
+ ExpandVirtualGeneratedColumns(copyObject(indexInfo->ii_Expressions),
+ NULL, relId);
}
/*
diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index eb383812..b47daaf5 100644
--- a/src/backend/executor/execIndexing.c
+++ b/src/backend/executor/execIndexing.c
@@ -380,20 +380,24 @@ ExecInsertIndexTuples(ResultRelInfo *resultRelInfo,
if (indexInfo->ii_Predicate != NIL)
{
ExprState *predicate;
+ ExprState *predicateExpand;
/*
* If predicate state not set up yet, create it (in the estate's
* per-query context)
*/
predicate = indexInfo->ii_PredicateState;
+ predicateExpand = indexInfo->ii_PredicateExpandState;
if (predicate == NULL)
{
predicate = ExecPrepareQual(indexInfo->ii_Predicate, estate);
+ predicateExpand = ExecPrepareQual(indexInfo->ii_PredicateExpand, estate);
indexInfo->ii_PredicateState = predicate;
+ indexInfo->ii_PredicateExpandState = predicateExpand;
}
/* Skip this index-update if the predicate isn't satisfied */
- if (!ExecQual(predicate, econtext))
+ if (!ExecQual(predicateExpand, econtext))
continue;
}
@@ -616,20 +620,24 @@ ExecCheckIndexConstraints(ResultRelInfo *resultRelInfo, TupleTableSlot *slot,
if (indexInfo->ii_Predicate != NIL)
{
ExprState *predicate;
+ ExprState *predicateExpand;
/*
* If predicate state not set up yet, create it (in the estate's
* per-query context)
*/
predicate = indexInfo->ii_PredicateState;
+ predicateExpand = indexInfo->ii_PredicateExpandState;
if (predicate == NULL)
{
predicate = ExecPrepareQual(indexInfo->ii_Predicate, estate);
+ predicateExpand = ExecPrepareQual(indexInfo->ii_PredicateExpand, estate);
indexInfo->ii_PredicateState = predicate;
+ indexInfo->ii_PredicateExpandState = predicateExpand;
}
/* Skip this index-update if the predicate isn't satisfied */
- if (!ExecQual(predicate, econtext))
+ if (!ExecQual(predicateExpand, econtext))
continue;
}
@@ -1102,6 +1110,7 @@ index_unchanged_by_update(ResultRelInfo *resultRelInfo, EState *estate,
* pass hint.
*/
idxExprs = RelationGetIndexExpressions(indexRelation);
+ idxExprs = list_concat(idxExprs, RelationGetIndexExpressionsExpand(indexRelation));
hasexpression = index_expression_changed_walker((Node *) idxExprs,
allUpdatedCols);
list_free(idxExprs);
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 40b09958..3aac6b51 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -828,7 +828,11 @@ make_ands_implicit(Expr *clause)
/*
* makeIndexInfo
- * create an IndexInfo node
+ * create an IndexInfo node. Upon returning from this function,
+ * callers must apply ExpandVirtualGeneratedColumns()
+ * or RelationGetIndexExpressionsExpand or RelationGetIndexPredicateExpand() to
+ * ii_ExpressionsExpand and ii_PredicateExpand as needed for actual
+ * expansion when they are not NIL or dummy.
*/
IndexInfo *
makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
@@ -856,11 +860,15 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
/* expressions */
n->ii_Expressions = expressions;
+ n->ii_ExpressionsExpand = copyObject(expressions);
n->ii_ExpressionsState = NIL;
+ n->ii_ExpressionsExpandState = NIL;
/* predicates */
n->ii_Predicate = predicates;
+ n->ii_PredicateExpand = copyObject(predicates);
n->ii_PredicateState = NULL;
+ n->ii_PredicateExpandState = NULL;
/* exclusion constraints */
n->ii_ExclusionOps = NULL;
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 1c575e56..be5c2e69 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -569,6 +569,7 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count,
double rand_heap_pages;
double index_pages;
uint64 enable_mask;
+ Cardinality saveBaserelRows;
/* Should only be applied to base relations */
Assert(IsA(baserel, RelOptInfo) &&
@@ -583,6 +584,7 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count,
* baserestrictinfo as the list of relevant restriction clauses for the
* rel.
*/
+ saveBaserelRows = baserel->rows;
if (path->path.param_info)
{
path->path.rows = path->path.param_info->ppi_rows;
@@ -594,7 +596,14 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count,
}
else
{
- path->path.rows = baserel->rows;
+ /*
+ * If index->predOK holds true, neither the baserel's estimated output tuples
+ * nor this index path's estimated output tuples can exceed the index's total tuples.
+ */
+ if (!index->predOK)
+ path->path.rows = baserel->rows;
+ else
+ path->path.rows = baserel->rows = index->tuples;
/* qpquals come from just the rel's restriction clauses */
qpquals = extract_nonindex_conditions(path->indexinfo->indrestrictinfo,
path->indexclauses);
@@ -772,8 +781,10 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count,
* doing extra computation.
*/
if (path->path.parallel_workers <= 0)
+ {
+ baserel->rows = saveBaserelRows;
return;
-
+ }
path->path.parallel_aware = true;
}
@@ -817,6 +828,8 @@ cost_index(IndexPath *path, PlannerInfo *root, double loop_count,
path->path.startup_cost = startup_cost;
path->path.total_cost = startup_cost + run_cost;
+
+ baserel->rows = saveBaserelRows;
}
/*
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 3f5d4fa3..23117e4a 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -267,7 +267,7 @@ create_index_paths(PlannerInfo *root, RelOptInfo *rel)
* (generate_bitmap_or_paths() might be able to do something with
* them, but that's of no concern here.)
*/
- if (index->indpred != NIL && !index->predOK)
+ if (index->indpredExpand != NIL && !index->predOK)
continue;
/*
@@ -1119,7 +1119,7 @@ build_paths_for_OR(PlannerInfo *root, RelOptInfo *rel,
* just scanning the predOK index alone, no OR.)
*/
useful_predicate = false;
- if (index->indpred != NIL)
+ if (index->indpredExpand != NIL)
{
if (index->predOK)
{
@@ -1131,10 +1131,10 @@ build_paths_for_OR(PlannerInfo *root, RelOptInfo *rel,
if (all_clauses == NIL)
all_clauses = list_concat_copy(clauses, other_clauses);
- if (!predicate_implied_by(index->indpred, all_clauses, false))
+ if (!predicate_implied_by(index->indpredExpand, all_clauses, false))
continue; /* can't use it at all */
- if (!predicate_implied_by(index->indpred, other_clauses, false))
+ if (!predicate_implied_by(index->indpredExpand, other_clauses, false))
useful_predicate = true;
}
}
@@ -2183,7 +2183,7 @@ find_indexpath_quals(Path *bitmapqual, List **quals, List **preds)
*quals = lappend(*quals, iclause->rinfo->clause);
}
- *preds = list_concat(*preds, ipath->indexinfo->indpred);
+ *preds = list_concat(*preds, ipath->indexinfo->indpredExpand);
}
else
elog(ERROR, "unrecognized node type: %d", nodeTag(bitmapqual));
@@ -3959,7 +3959,7 @@ check_index_predicates(PlannerInfo *root, RelOptInfo *rel)
IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
index->indrestrictinfo = rel->baserestrictinfo;
- if (index->indpred)
+ if (index->indpredExpand)
have_partial = true;
}
if (!have_partial)
@@ -4038,11 +4038,11 @@ check_index_predicates(PlannerInfo *root, RelOptInfo *rel)
IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
ListCell *lcr;
- if (index->indpred == NIL)
+ if (index->indpredExpand == NIL)
continue; /* ignore non-partial indexes here */
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->indpredExpand, clauselist,
false);
/* If rel is an update target, leave indrestrictinfo as set above */
@@ -4068,7 +4068,7 @@ check_index_predicates(PlannerInfo *root, RelOptInfo *rel)
/* predicate_implied_by() assumes first arg is immutable */
if (contain_mutable_functions((Node *) rinfo->clause) ||
!predicate_implied_by(list_make1(rinfo->clause),
- index->indpred, false))
+ index->indpredExpand, false))
index->indrestrictinfo = lappend(index->indrestrictinfo, rinfo);
}
}
@@ -4402,14 +4402,14 @@ match_index_to_operand(Node *operand,
int i;
Node *indexkey;
- indexpr_item = list_head(index->indexprs);
+ indexpr_item = list_head(index->indexprsExpand);
for (i = 0; i < indexcol; i++)
{
if (index->indexkeys[i] == 0)
{
if (indexpr_item == NULL)
elog(ERROR, "wrong number of index expressions");
- indexpr_item = lnext(index->indexprs, indexpr_item);
+ indexpr_item = lnext(index->indexprsExpand, indexpr_item);
}
}
if (indexpr_item == NULL)
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index de6a183d..2bdad7bd 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -3340,7 +3340,7 @@ create_bitmap_subplan(PlannerInfo *root, Path *bitmapqual,
subindexECs = lappend(subindexECs, rinfo->parent_ec);
}
/* We can add any index predicate conditions, too */
- foreach(l, ipath->indexinfo->indpred)
+ foreach(l, ipath->indexinfo->indpredExpand)
{
Expr *pred = (Expr *) lfirst(l);
@@ -5142,7 +5142,7 @@ fix_indexqual_operand(Node *node, IndexOptInfo *index, int indexcol)
}
/* It's an index expression, so find and cross-check the expression */
- indexpr_item = list_head(index->indexprs);
+ indexpr_item = list_head(index->indexprsExpand);
for (pos = 0; pos < index->ncolumns; pos++)
{
if (index->indexkeys[pos] == 0)
@@ -5167,7 +5167,7 @@ fix_indexqual_operand(Node *node, IndexOptInfo *index, int indexcol)
else
elog(ERROR, "index key does not match expected index column");
}
- indexpr_item = lnext(index->indexprs, indexpr_item);
+ indexpr_item = lnext(index->indexprsExpand, indexpr_item);
}
}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 7c4be174..72846d9e 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -433,24 +433,38 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
* properly reduced.
*/
info->indexprs = RelationGetIndexExpressions(indexRelation);
+ info->indexprsExpand = RelationGetIndexExpressionsExpand(indexRelation);
info->indpred = RelationGetIndexPredicate(indexRelation);
+ info->indpredExpand = RelationGetIndexPredicateExpand(indexRelation);
if (info->indexprs)
{
if (varno != 1)
+ {
ChangeVarNodes((Node *) info->indexprs, 1, varno, 0);
+ ChangeVarNodes((Node *) info->indexprsExpand, 1, varno, 0);
+ }
info->indexprs = (List *)
eval_const_expressions(root, (Node *) info->indexprs);
+ info->indexprsExpand = (List *)
+ eval_const_expressions(root, (Node *) info->indexprsExpand);
}
if (info->indpred)
{
if (varno != 1)
+ {
ChangeVarNodes((Node *) info->indpred, 1, varno, 0);
+ ChangeVarNodes((Node *) info->indpredExpand, 1, varno, 0);
+ }
info->indpred = (List *)
eval_const_expressions(root,
(Node *) make_ands_explicit(info->indpred));
+ info->indpredExpand = (List *)
+ eval_const_expressions(root,
+ (Node *) make_ands_explicit(info->indpredExpand));
info->indpred = make_ands_implicit((Expr *) info->indpred);
+ info->indpredExpand = make_ands_implicit((Expr *) info->indpredExpand);
}
/* Build targetlist using the completed indexprs data */
@@ -941,8 +955,8 @@ infer_arbiter_indexes(PlannerInfo *root)
attno - FirstLowInvalidHeapAttributeNumber);
}
- inferElems = RelationGetIndexExpressions(idxRel);
- inferIndexExprs = RelationGetIndexPredicate(idxRel);
+ inferElems = RelationGetIndexExpressionsExpand(idxRel);
+ inferIndexExprs = RelationGetIndexPredicateExpand(idxRel);
break;
}
}
@@ -1072,7 +1086,7 @@ infer_arbiter_indexes(PlannerInfo *root)
continue;
/* Expression attributes (if any) must match */
- idxExprs = RelationGetIndexExpressions(idxRel);
+ idxExprs = RelationGetIndexExpressionsExpand(idxRel);
if (idxExprs)
{
if (varno != 1)
@@ -1139,7 +1153,7 @@ infer_arbiter_indexes(PlannerInfo *root)
if (list_difference(idxExprs, inferElems) != NIL)
continue;
- predExprs = RelationGetIndexPredicate(idxRel);
+ predExprs = RelationGetIndexPredicateExpand(idxRel);
if (predExprs)
{
if (varno != 1)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index d6efd070..01e6b5b1 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -7431,6 +7431,7 @@ genericcostestimate(PlannerInfo *root,
double qual_arg_cost;
List *selectivityQuals;
ListCell *l;
+ bool needCalcIndexSelectivity = true;
/*
* If the index is partial, AND the index predicate with the explicitly
@@ -7495,9 +7496,23 @@ genericcostestimate(PlannerInfo *root,
* indexSelectivity estimate is tiny.
*/
if (numIndexTuples > index->tuples)
+ {
numIndexTuples = index->tuples;
+ needCalcIndexSelectivity = true;
+ }
+
+ /* Recalculate indexSelectivity based on the estimated numIndexTuples */
if (numIndexTuples < 1.0)
+ {
numIndexTuples = 1.0;
+ indexSelectivity = 0.0;
+ }
+ else if (needCalcIndexSelectivity && index->rel->tuples > 0)
+ {
+ Selectivity newIndexSelectivity = numIndexTuples / index->rel->tuples;
+ if (indexSelectivity > newIndexSelectivity)
+ indexSelectivity = newIndexSelectivity;
+ }
/*
* Estimate the number of index pages that will be retrieved.
@@ -7639,10 +7654,10 @@ add_predicate_to_index_quals(IndexOptInfo *index, List *indexQuals)
List *predExtraQuals = NIL;
ListCell *lc;
- if (index->indpred == NIL)
+ if (index->indpredExpand == NIL)
return indexQuals;
- foreach(lc, index->indpred)
+ foreach(lc, index->indpredExpand)
{
Node *predQual = (Node *) lfirst(lc);
List *oneQual = list_make1(predQual);
@@ -8054,6 +8069,12 @@ btcostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
JOIN_INNER,
NULL);
numIndexTuples = btreeSelectivity * index->rel->tuples;
+ /*
+ * The number of leaf tuples visited for cost
+ * cannot exceed the total tuples of the index.
+ */
+ if (numIndexTuples > index->tuples)
+ numIndexTuples = index->tuples;
/*
* btree automatically combines individual array element primitive
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 0572ab42..e604bb91 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -91,6 +91,7 @@
#include "utils/resowner.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
+#include "rewrite/rewriteHandler.h"
#define RELCACHE_INIT_FILEMAGIC 0x573266 /* version ID value */
@@ -1583,7 +1584,9 @@ RelationInitIndexAccessInfo(Relation relation)
* expressions, predicate, exclusion caches will be filled later
*/
relation->rd_indexprs = NIL;
+ relation->rd_indexprsExpand = NIL;
relation->rd_indpred = NIL;
+ relation->rd_indpredExpand = NIL;
relation->rd_exclops = NULL;
relation->rd_exclprocs = NULL;
relation->rd_exclstrats = NULL;
@@ -5146,6 +5149,45 @@ RelationGetIndexExpressions(Relation relation)
return result;
}
+List *
+RelationGetIndexExpressionsExpand(Relation relation)
+{
+ List *result;
+ MemoryContext oldcxt;
+ bool isnull;
+ Datum heapRelidDatum;
+ Oid heapRelid;
+
+ /* Quick exit if we already computed the result. */
+ if (relation->rd_indexprsExpand)
+ return copyObject(relation->rd_indexprsExpand);
+
+ /* Quick exit if there is nothing to do. */
+ if (relation->rd_indextuple == NULL ||
+ heap_attisnull(relation->rd_indextuple, Anum_pg_index_indexprs, NULL))
+ return NIL;
+
+ result = RelationGetIndexExpressions(relation);
+ if (result == NIL)
+ return NIL;
+
+ heapRelidDatum = heap_getattr(relation->rd_indextuple,
+ Anum_pg_index_indrelid,
+ GetPgIndexDescriptor(),
+ &isnull);
+ Assert(!isnull);
+ heapRelid = DatumGetObjectId(heapRelidDatum);
+
+ result = ExpandVirtualGeneratedColumns(result, NULL, heapRelid);
+
+ /* Now save a copy of the completed tree in the relcache entry. */
+ oldcxt = MemoryContextSwitchTo(relation->rd_indexcxt);
+ relation->rd_indexprsExpand = copyObject(result);
+ MemoryContextSwitchTo(oldcxt);
+
+ return result;
+}
+
/*
* RelationGetDummyIndexExpressions -- get dummy expressions for an index
*
@@ -5266,6 +5308,45 @@ RelationGetIndexPredicate(Relation relation)
return result;
}
+List *
+RelationGetIndexPredicateExpand(Relation relation)
+{
+ List *result;
+ MemoryContext oldcxt;
+ bool isnull;
+ Datum heapRelidDatum;
+ Oid heapRelid;
+
+ /* Quick exit if we already computed the result. */
+ if (relation->rd_indpredExpand)
+ return copyObject(relation->rd_indpredExpand);
+
+ /* Quick exit if there is nothing to do. */
+ if (relation->rd_indextuple == NULL ||
+ heap_attisnull(relation->rd_indextuple, Anum_pg_index_indpred, NULL))
+ return NIL;
+
+ result = RelationGetIndexPredicate(relation);
+ if (result == NIL)
+ return NIL;
+
+ heapRelidDatum = heap_getattr(relation->rd_indextuple,
+ Anum_pg_index_indrelid,
+ GetPgIndexDescriptor(),
+ &isnull);
+ Assert(!isnull);
+ heapRelid = DatumGetObjectId(heapRelidDatum);
+
+ result = ExpandVirtualGeneratedColumns(result, NULL, heapRelid);
+
+ /* Now save a copy of the completed tree in the relcache entry. */
+ oldcxt = MemoryContextSwitchTo(relation->rd_indexcxt);
+ relation->rd_indpredExpand = copyObject(result);
+ MemoryContextSwitchTo(oldcxt);
+
+ return result;
+}
+
/*
* RelationGetIndexAttrBitmap -- get a bitmap of index attribute numbers
*
@@ -6490,7 +6571,9 @@ load_relcache_init_file(bool shared)
rel->rd_partcheckvalid = false;
rel->rd_partcheckcxt = NULL;
rel->rd_indexprs = NIL;
+ rel->rd_indexprsExpand = NIL;
rel->rd_indpred = NIL;
+ rel->rd_indpredExpand = NIL;
rel->rd_exclops = NULL;
rel->rd_exclprocs = NULL;
rel->rd_exclstrats = NULL;
@@ -7019,3 +7102,46 @@ ResOwnerReleaseRelation(Datum res)
RelationCloseCleanup((Relation) DatumGetPointer(res));
}
+
+List *
+ExpandVirtualGeneratedColumns(List *list, Relation heapRelation, Oid heapRelId)
+{
+ bool opened_relation = false;
+ TupleDesc tupdesc;
+
+ if (list == NIL || (heapRelation == NULL && heapRelId == InvalidOid))
+ return list;
+
+ if (heapRelation == NULL)
+ {
+ heapRelation = table_open(heapRelId, NoLock);
+ opened_relation = true;
+ }
+
+ tupdesc = RelationGetDescr(heapRelation);
+ if ((tupdesc->constr && tupdesc->constr->has_generated_virtual))
+ {
+ int j;
+ Bitmapset *indexattrs = NULL;
+
+ pull_varattnos((Node *)list, 1, &indexattrs);
+
+ j = -1;
+ while ((j = bms_next_member(indexattrs, j)) >= 0)
+ {
+ AttrNumber attno = j + FirstLowInvalidHeapAttributeNumber;
+
+ if (attno > 0 &&
+ TupleDescAttr(tupdesc, attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ list = (List *)expand_generated_columns_in_expr((Node *)list, heapRelation, 1);
+ break;
+ }
+ }
+ }
+
+ if (opened_relation)
+ table_close(heapRelation, NoLock);
+
+ return list;
+}
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 53c13831..20a2e618 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -190,13 +190,17 @@ typedef struct IndexInfo
/* expr trees for expression entries, or NIL if none */
List *ii_Expressions; /* list of Expr */
+ List *ii_ExpressionsExpand; /* list of Expr */
/* exec state for expressions, or NIL if none */
List *ii_ExpressionsState; /* list of ExprState */
+ List *ii_ExpressionsExpandState; /* list of ExprState */
/* partial-index predicate, or NIL if none */
List *ii_Predicate; /* list of Expr */
+ List *ii_PredicateExpand; /* list of Expr */
/* exec state for expressions, or NIL if none */
ExprState *ii_PredicateState;
+ ExprState *ii_PredicateExpandState;
/* Per-column exclusion operators, or NULL if none */
Oid *ii_ExclusionOps; /* array with one entry per column */
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 27a2c681..9c65e123 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1403,8 +1403,10 @@ typedef struct IndexOptInfo
* print indextlist
*/
List *indexprs pg_node_attr(read_write_ignore);
+ List *indexprsExpand pg_node_attr(read_write_ignore);
/* predicate if a partial index, else NIL */
List *indpred;
+ List *indpredExpand;
/* targetlist representing index columns */
List *indextlist;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index fa07ebf8..7bd01b36 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -210,7 +210,9 @@ typedef struct RelationData
struct FmgrInfo *rd_supportinfo; /* lookup info for support procedures */
int16 *rd_indoption; /* per-column AM-specific flags */
List *rd_indexprs; /* index expression trees, if any */
+ List *rd_indexprsExpand; /* expanded index expression trees, if any */
List *rd_indpred; /* index predicate tree, if any */
+ List *rd_indpredExpand; /* expanded index predicate tree, if any */
Oid *rd_exclops; /* OIDs of exclusion operators, if any */
Oid *rd_exclprocs; /* OIDs of exclusion ops' procs, if any */
uint16 *rd_exclstrats; /* exclusion ops' strategy numbers, if any */
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index 89c27aa1..70531bb8 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -58,8 +58,10 @@ extern List *RelationGetStatExtList(Relation relation);
extern Oid RelationGetPrimaryKeyIndex(Relation relation, bool deferrable_ok);
extern Oid RelationGetReplicaIndex(Relation relation);
extern List *RelationGetIndexExpressions(Relation relation);
+extern List *RelationGetIndexExpressionsExpand(Relation relation);
extern List *RelationGetDummyIndexExpressions(Relation relation);
extern List *RelationGetIndexPredicate(Relation relation);
+extern List *RelationGetIndexPredicateExpand(Relation relation);
extern bytea **RelationGetIndexAttOptions(Relation relation, bool copy);
/*
@@ -161,4 +163,6 @@ extern PGDLLIMPORT bool criticalRelcachesBuilt;
/* should be used only by relcache.c and postinit.c */
extern PGDLLIMPORT bool criticalSharedRelcachesBuilt;
+extern List *ExpandVirtualGeneratedColumns(List *list, Relation heapRelation, Oid heapRelId);
+
#endif /* RELCACHE_H */
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index 24d5dbf4..cd00a43d 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -370,6 +370,7 @@ Not-null constraints:
"gtest1_a_not_null" NOT NULL "a" (inherited)
Inherits:
gtest1
+Access method: heap
INSERT INTO gtestx (a, x) VALUES (11, 22);
SELECT * FROM gtest1;
@@ -763,39 +764,202 @@ ERROR: column "c" of relation "gtestnn_child" contains null values
ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) VIRTUAL; -- ok
-- index constraints
CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE);
-ERROR: unique constraints on virtual generated columns are not supported
---INSERT INTO gtest22a VALUES (2);
---INSERT INTO gtest22a VALUES (3);
---INSERT INTO gtest22a VALUES (4);
+INSERT INTO gtest22a VALUES (2); -- ok
+INSERT INTO gtest22a VALUES (3); -- error
+ERROR: duplicate key value violates unique constraint "gtest22a_b_key"
+DETAIL: Key (b)=(1) already exists.
+INSERT INTO gtest22a VALUES (4); -- ok
CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) VIRTUAL, PRIMARY KEY (a, b));
-ERROR: primary keys on virtual generated columns are not supported
---INSERT INTO gtest22b VALUES (2);
---INSERT INTO gtest22b VALUES (2);
+INSERT INTO gtest22b VALUES (2); -- ok
+INSERT INTO gtest22b VALUES (2); -- error
+ERROR: duplicate key value violates unique constraint "gtest22b_pkey"
+DETAIL: Key (a, b)=(2, 1) already exists.
-- indexes
CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
---CREATE INDEX gtest22c_b_idx ON gtest22c (b);
---CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
---CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
---\d gtest22c
---INSERT INTO gtest22c VALUES (1), (2), (3);
---SET enable_seqscan TO off;
---SET enable_bitmapscan TO off;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
---SELECT * FROM gtest22c WHERE b = 4;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
---SELECT * FROM gtest22c WHERE b * 3 = 6;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
---SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
---ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
---ANALYZE gtest22c;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
---SELECT * FROM gtest22c WHERE b = 8;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
---SELECT * FROM gtest22c WHERE b * 3 = 12;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
---SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
---RESET enable_seqscan;
---RESET enable_bitmapscan;
+CREATE INDEX gtest22c_b_idx ON gtest22c (b); -- ok
+CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3)); -- ok
+CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0; -- ok
+\d gtest22c
+ Table "generated_virtual_tests.gtest22c"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+Indexes:
+ "gtest22c_b_idx" btree (b)
+ "gtest22c_expr_idx" btree ((b * 3))
+ "gtest22c_pred_idx" btree (a) WHERE b > 0
+
+INSERT INTO gtest22c VALUES (1), (2), (3);
+SET enable_seqscan TO off;
+SET enable_bitmapscan TO off;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
+ QUERY PLAN
+-----------------------------------------------------
+ Index Only Scan using gtest22c_pred_idx on gtest22c
+ Filter: ((a * 2) = 4)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE b = 4;
+ a | b
+---+---
+ 2 | 4
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
+ QUERY PLAN
+------------------------------------------------
+ Index Scan using gtest22c_expr_idx on gtest22c
+ Index Cond: (((a * 2) * 3) = 6)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE b * 3 = 6;
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+ QUERY PLAN
+-----------------------------------------------------
+ Index Only Scan using gtest22c_pred_idx on gtest22c
+ Index Cond: (a = 1)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
+ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
+ANALYZE gtest22c;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
+ QUERY PLAN
+-----------------------------------------------------
+ Index Only Scan using gtest22c_pred_idx on gtest22c
+ Filter: ((a * 4) = 8)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE b = 8;
+ a | b
+---+---
+ 2 | 8
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
+ QUERY PLAN
+------------------------------------------------
+ Index Scan using gtest22c_expr_idx on gtest22c
+ Index Cond: (((a * 4) * 3) = 12)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE b * 3 = 12;
+ a | b
+---+---
+ 1 | 4
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+ QUERY PLAN
+-----------------------------------------------------
+ Index Only Scan using gtest22c_pred_idx on gtest22c
+ Index Cond: (a = 1)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+ a | b
+---+---
+ 1 | 4
+(1 row)
+
+CREATE TABLE gtest22d (a int
+ ,v1 int GENERATED ALWAYS AS (a * 11) VIRTUAL
+ ,v2 int GENERATED ALWAYS AS (a * 22) VIRTUAL
+ ,v3 int GENERATED ALWAYS AS (a * 33) VIRTUAL
+ ,v4 int GENERATED ALWAYS AS (a * 44) VIRTUAL
+); -- ok
+insert into gtest22d select oid from pg_catalog.pg_class union all select -1; -- ok
+CREATE INDEX gtest22d_vgc_idx ON gtest22d(a,v1,abs(v2)) Include(v3) WHERE v4 < 100; -- ok
+\d gtest22d
+ Table "generated_virtual_tests.gtest22d"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+------------------------------
+ a | integer | | |
+ v1 | integer | | | generated always as (a * 11)
+ v2 | integer | | | generated always as (a * 22)
+ v3 | integer | | | generated always as (a * 33)
+ v4 | integer | | | generated always as (a * 44)
+Indexes:
+ "gtest22d_vgc_idx" btree (a, v1, abs(v2)) INCLUDE (v3) WHERE v4 < 100
+
+analyze gtest22d;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE v4 < 100; --Index Only Scan or Index Scan
+ QUERY PLAN
+----------------------------------------------------
+ Index Only Scan using gtest22d_vgc_idx on gtest22d
+(1 row)
+
+SELECT * FROM gtest22d WHERE v4 < 100;
+ a | v1 | v2 | v3 | v4
+----+-----+-----+-----+-----
+ -1 | -11 | -22 | -33 | -44
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE v4 * 5 < 100; --Seq Scan
+ QUERY PLAN
+----------------------------------
+ Seq Scan on gtest22d
+ Disabled: true
+ Filter: (((a * 44) * 5) < 100)
+(3 rows)
+
+SELECT * FROM gtest22d WHERE v4 * 5 < 100;
+ a | v1 | v2 | v3 | v4
+----+-----+-----+-----+-----
+ -1 | -11 | -22 | -33 | -44
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE a * 44 < 100; --Index Only Scan or Index Scan
+ QUERY PLAN
+----------------------------------------------------
+ Index Only Scan using gtest22d_vgc_idx on gtest22d
+(1 row)
+
+SELECT * FROM gtest22d WHERE a * 44 < 100;
+ a | v1 | v2 | v3 | v4
+----+-----+-----+-----+-----
+ -1 | -11 | -22 | -33 | -44
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE (a * 44) < 100; --Index Only Scan or Index Scan
+ QUERY PLAN
+----------------------------------------------------
+ Index Only Scan using gtest22d_vgc_idx on gtest22d
+(1 row)
+
+SELECT * FROM gtest22d WHERE (a * 44) < 100;
+ a | v1 | v2 | v3 | v4
+----+-----+-----+-----+-----
+ -1 | -11 | -22 | -33 | -44
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE (a * 33) < 100; --Seq Scan
+ QUERY PLAN
+----------------------------
+ Seq Scan on gtest22d
+ Disabled: true
+ Filter: ((a * 33) < 100)
+(3 rows)
+
+SELECT * FROM gtest22d WHERE (a * 33) < 100;
+ a | v1 | v2 | v3 | v4
+----+-----+-----+-----+-----
+ -1 | -11 | -22 | -33 | -44
+(1 row)
+
+RESET enable_seqscan;
+RESET enable_bitmapscan;
-- foreign keys
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
--INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
@@ -813,12 +977,12 @@ ERROR: foreign key constraints on virtual generated columns are not supported
--DROP TABLE gtest23b;
--DROP TABLE gtest23a;
CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) VIRTUAL, PRIMARY KEY (y));
-ERROR: primary keys on virtual generated columns are not supported
---INSERT INTO gtest23p VALUES (1), (2), (3);
+INSERT INTO gtest23p VALUES (1), (2), (3); -- ok
CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));
-ERROR: relation "gtest23p" does not exist
---INSERT INTO gtest23q VALUES (1, 2); -- ok
---INSERT INTO gtest23q VALUES (2, 5); -- error
+INSERT INTO gtest23q VALUES (1, 2); -- ok
+INSERT INTO gtest23q VALUES (2, 5); -- error
+ERROR: insert or update on table "gtest23q" violates foreign key constraint "gtest23q_b_fkey"
+DETAIL: Key (b)=(5) is not present in table "gtest23p".
-- domains
CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) VIRTUAL);
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index 9c2bb659..5ebdc5ef 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -396,40 +396,62 @@ ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif
-- index constraints
CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE);
---INSERT INTO gtest22a VALUES (2);
---INSERT INTO gtest22a VALUES (3);
---INSERT INTO gtest22a VALUES (4);
+INSERT INTO gtest22a VALUES (2); -- ok
+INSERT INTO gtest22a VALUES (3); -- error
+INSERT INTO gtest22a VALUES (4); -- ok
CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) VIRTUAL, PRIMARY KEY (a, b));
---INSERT INTO gtest22b VALUES (2);
---INSERT INTO gtest22b VALUES (2);
+INSERT INTO gtest22b VALUES (2); -- ok
+INSERT INTO gtest22b VALUES (2); -- error
-- indexes
CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
---CREATE INDEX gtest22c_b_idx ON gtest22c (b);
---CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
---CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
---\d gtest22c
-
---INSERT INTO gtest22c VALUES (1), (2), (3);
---SET enable_seqscan TO off;
---SET enable_bitmapscan TO off;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
---SELECT * FROM gtest22c WHERE b = 4;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
---SELECT * FROM gtest22c WHERE b * 3 = 6;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
---SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
-
---ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
---ANALYZE gtest22c;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
---SELECT * FROM gtest22c WHERE b = 8;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
---SELECT * FROM gtest22c WHERE b * 3 = 12;
---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
---SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
---RESET enable_seqscan;
---RESET enable_bitmapscan;
+CREATE INDEX gtest22c_b_idx ON gtest22c (b); -- ok
+CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3)); -- ok
+CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0; -- ok
+\d gtest22c
+
+INSERT INTO gtest22c VALUES (1), (2), (3);
+SET enable_seqscan TO off;
+SET enable_bitmapscan TO off;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
+SELECT * FROM gtest22c WHERE b = 4;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
+SELECT * FROM gtest22c WHERE b * 3 = 6;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+
+ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
+ANALYZE gtest22c;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
+SELECT * FROM gtest22c WHERE b = 8;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
+SELECT * FROM gtest22c WHERE b * 3 = 12;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
+
+CREATE TABLE gtest22d (a int
+ ,v1 int GENERATED ALWAYS AS (a * 11) VIRTUAL
+ ,v2 int GENERATED ALWAYS AS (a * 22) VIRTUAL
+ ,v3 int GENERATED ALWAYS AS (a * 33) VIRTUAL
+ ,v4 int GENERATED ALWAYS AS (a * 44) VIRTUAL
+); -- ok
+insert into gtest22d select oid from pg_catalog.pg_class union all select -1; -- ok
+CREATE INDEX gtest22d_vgc_idx ON gtest22d(a,v1,abs(v2)) Include(v3) WHERE v4 < 100; -- ok
+\d gtest22d
+analyze gtest22d;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE v4 < 100; --Index Only Scan or Index Scan
+SELECT * FROM gtest22d WHERE v4 < 100;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE v4 * 5 < 100; --Seq Scan
+SELECT * FROM gtest22d WHERE v4 * 5 < 100;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE a * 44 < 100; --Index Only Scan or Index Scan
+SELECT * FROM gtest22d WHERE a * 44 < 100;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE (a * 44) < 100; --Index Only Scan or Index Scan
+SELECT * FROM gtest22d WHERE (a * 44) < 100;
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22d WHERE (a * 33) < 100; --Seq Scan
+SELECT * FROM gtest22d WHERE (a * 33) < 100;
+
+RESET enable_seqscan;
+RESET enable_bitmapscan;
-- foreign keys
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
@@ -450,11 +472,11 @@ CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRT
--DROP TABLE gtest23a;
CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) VIRTUAL, PRIMARY KEY (y));
---INSERT INTO gtest23p VALUES (1), (2), (3);
+INSERT INTO gtest23p VALUES (1), (2), (3); -- ok
CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));
---INSERT INTO gtest23q VALUES (1, 2); -- ok
---INSERT INTO gtest23q VALUES (2, 5); -- error
+INSERT INTO gtest23q VALUES (1, 2); -- ok
+INSERT INTO gtest23q VALUES (2, 5); -- error
-- domains
CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
--
2.43.0
[application/octet-stream] v2-相关成员、函数的整体分析.xlsx (47.5K, ../[email protected]/3-v2-%E7%9B%B8%E5%85%B3%E6%88%90%E5%91%98%E3%80%81%E5%87%BD%E6%95%B0%E7%9A%84%E6%95%B4%E4%BD%93%E5%88%86%E6%9E%90.xlsx)
download
[application/octet-stream] v2-Comprehensive Analysis of Related Members and Functions.xlsx (47.4K, ../[email protected]/4-v2-Comprehensive%20Analysis%20of%20Related%20Members%20and%20Functions.xlsx)
download
view thread (10+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: support create index on virtual generated column.
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox