Subject: Re: support create index on virtual generated column.

From: "ZizhuanLiu X-MAN" <[email-masked]>
Date: Sun, 5 Jul 2026 11:04:09 +0800

>On Fri, Mar 13, 2026 at 10:01?PM Peter Eisentraut <[email-masked]> wrote:
>>
>> I think you could do a much simpler initial version of this if you just
>> supported virtual generated columns in expression indexes.  And then
>> prohibit SET EXPRESSION if the column is used in an index.  Then you
>> don't need to worry about index rebuilding, ALTER TABLE recursion, new
>> catalog columns, and all that.
>>
>
>CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a) VIRTUAL);
>CREATE INDEX gtest22c_a_idx ON gtest22c (a);
>CREATE INDEX gtest22c_b_idx ON gtest22c (b);
>
>If we don't add a new catalog column (just a single boolean
>indisvirtual is not enough, i think),
>how can we distinguish between the gtest22c_a_idx and gtest22c_b_idx
>indexes in the example above?
>
>If CREATE INDEX simply expands the virtual generated column expression
>without dependency tracking, that would be quite easy, see the
>attached v8.
>If so, we need to explicitly document that SET EXPRESSION has no
>effect on existing indexes that originally referenced the virtual
>generated column when CREATE INDEX was used.
>
>> But there is a comment in DefineIndex():
>>
>>      /*
>>       * XXX Virtual generated columns in index expressions or predicates
>>       * could be supported, but it needs support in
>>       * RelationGetIndexExpressions() and RelationGetIndexPredicate().
>>       */
>>
>> which you delete, but you don't make any changes to those mentioned
>> functions.  Maybe the comment is wrong, in which case, let's discuss
>> that and fix it.  (If the comment is indeed wrong, then the feature
>> might even be very easy.)
>>
>
>I don't think it's a good idea to store the virtual generated columns
>as is in Form_pg_index->indkey
>because IndexInfo->ii_IndexAttrNumbers and Form->pg_index->indkey are
>referenced in too many places (see BuildIndexInfo).
>For every single occurrence of ndkey.values[i], we need to consider
>whether it's ok for it be a virtual generated column.
>Instead, Anum_pg_index_indexprs and Anum_pg_index_indpred store the
>expressions after the virtual generated columns expansion,
>then we don't need to worry about Form_pg_index->indkey.values[i] is
>virtual generated column or not.
>
>Therefore, i think RelationGetIndexExpressions and
>RelationGetIndexPredicate don't need to
>deal with virtual generated column expressions at all.
>Overall, I think the comment above is wrong.
>
>
>
>--
>jian
>https://www.enterprisedb.com/

Hi, hackers

Quick glossary:
VGC = virtual generated columns
SGC = stored generated columns

### 1. Iteration of implementation attempts
I first drafted V0: no VGC expansion during index creation, only temporary expansion at runtime. 
The scope of required changes kept expanding (see `v0.patch.txt`), so I abandoned this approach.

Next I tried V1, expanding VGCs directly on index creation to support VGC in index expressions,
`INCLUDE` columns and partial index `WHERE` clauses. Unfortunately this would break metadata
catalog immutability. Before finalizing the patch (see `v1.patch.txt`), I found prior community
discussions and prototype attempts with several major design revisions:
https://postgr.es/m/CACJufxGao-cypdNhifHAdt8jHfK6-HX=[email-masked]
https://postgr.es/m/CACJufxGgkH0PyyqP6ggqcEWHxZzmkV=puY8ad=[email-masked]
https://postgr.es/m/[email-masked]
https://www.postgresql.org/message-id/flat/CACJufxExe7%2BGh5MKMFiN5xwwmPXaaZC1jjQOFuovv8Q7b1LgFw%40mail.gmail.com#de1541048f0d8257248e7eb9cbb4a440
https://www.postgresql.org/message-id/flat/CACJufxGgkH0PyyqP6ggqcEWHxZzmkV%3DpuY8ad%3Ds8kisss9MAwg%40mail.gmail.com#6fe38ee2053af1ac025b1f8d9261a168
https://commitfest.postgresql.org/patch/5667
https://commitfest.postgresql.org/patch/6094/
https://www.postgresql.org/message-id/flat/CAMbWs4811nG3w_3sLxps%2BEAuUsffA_83ZQ-1acEH_jQeq117mg%40mail.gmail.com

I then redesigned for V2. Developing this version made it clear how complex the feature is. Every change
was reviewed for local behavior plus upstream/downstream impacts, though some edge cases may still be overlooked.

### 2. Core design principles
1. **Goal**: Enable VGC support for regular index columns, expression columns, `INCLUDE` columns and columns used
in partial index predicates.

2. **Immutable metadata catalog**: We must not alter existing catalog entries(see v2-SQL_test_result.xlsx). VGCs are
benchmarked against SGCs to guarantee no regressions for index rebuild, logical replication, index comparison, stats
collection and `pg_dump`.

3. **Runtime expansion required**: The parser expands VGCs during query analysis, while SGCs are stored unexpanded
at index creation. Without runtime expansion, the planner cannot properly select access paths, sort strategies or partial indexes.

I added paired expand fields and helper functions to `IndexInfo`, `RelationData` and `IndexOptInfo`. Expanded expressions
are built once instead of dynamically on each access, hence paired code paths for raw and expanded fields.

4. I updated comments for `ComputeIndexAttrs()` and `makeIndexInfo()`. These functions remain unmodified; we expand
the new fields right after calling them for later access safety.

5. Major changes to `FormIndexDatum`: VGC values are computed on the fly via expanded expressions and stored inindex
tuples, behaving like SGCs to support index key evaluation, INCLUDE columns, expression indexes, and partial index matching.

### 3. Main challenge
The most tricky part is distinguishing which code paths should use raw non-expanded nodes versus expanded ones,
which consumed most of my development effort.

### 4. Code review scope
I audited all code paths for the following functions/fields to choose expansion mode and assess side effects:
makeIndexInfo|indpredExpand|indpred|indexprsExpand|indexprs|ii_PredicateState|ii_PredicateExpandState|
ii_PredicateExpand|ii_Predicate|ii_ExpressionsState|ii_ExpressionsExpandState|ii_ExpressionsExpand|ii_Expressions|
RelationGetIndexPredicateExpand |RelationGetIndexPredicate|RelationGetIndexExpressionsExpand|
RelationGetIndexExpressions|FormIndexDatum|ComputeIndexAttrs|rd_indpredExpand|rd_indpred|rd_indexprsExpand|rd_indexprs.

All changes and behavioral impacts are recorded in 
v2-相关成员、函数的整体分析.xlsx    (Chinese)
v2-Comprehensive Analysis of Related Members and Functions.xlsx    (English).

### 5. Current status
While I have made substantial changes, full test coverage may still be incomplete. Community review, testing and
feedback are highly appreciated. Based on current progress and my testing (which is admittedly not comprehensive
or thorough), it aligns with SGCs in most metadata catalog aspects. see "v2-SQL_test_result.xlsx" including checking SQL.

Basic index scans now work, but there are still minor planner inconsistencies that I am debugging and resolving.  
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

Forexample:
xman5=# explain select  a from t1 where s4 < 100;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Index Only Scan using idx_t1_s on t1  (cost=0.13..8.14 rows=1 width=4)
(1 row)

xman5=# explain select  a from t1 where v4 < 100;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=4.18..10.48 rows=153 width=4)         -------still need heap Scan and Recheck Cond
   Recheck Cond: ((a * 44) < 100)
   ->  Bitmap Index Scan on idx_t1_v  (cost=0.00..4.14 rows=153 width=0)
(3 rows)

regards,
--
ZizhuanLiu (X-MAN) 
[email-masked]

---

From: "ZizhuanLiu X-MAN" <[email-masked]>
Date: Sun, 5 Jul 2026 17:00:53 +0800

I've checked the CFbot test results; failures are mostly
limitedto `generated_virtual.sql` and its related test cases.

regards,
--
ZizhuanLiu (X-MAN) 
[email-masked]

---

From: "ZizhuanLiu X-MAN" <[email-masked]>
Date: Tue, 7 Jul 2026 00:09:36 +0800

>Original
>From: ZizhuanLiu X-MAN <[email-masked]>
>Date: 2026-07-05 11:04
>To: jian he <[email-masked]>, Peter Eisentraut <[email-masked]>, pgsql-hackers <[email-masked]>
>Cc: Corey Huinker <[email-masked]>, Tom Lane <[email-masked]>, chengpeng_yan <[email-masked]>, guofenglinux <[email-masked]>, 我自己的邮箱 <[email-masked]>
>Subject: Re: support create index on virtual generated column.
>

Hi, 

I built further optimizations based on the V2 patch and supplemented logic
in the planner to align the execution plans of idx_t1_v and idx_t1_s.

In cost_index(), I am confident about setting path->path.rows = index->tuples 
when index->predOK == true. However, I’m not entirely sure whether we should
also assign index->tuples to baserel->rows.

Besides, I left the branch guarded by if (path->path.param_info) untouched. 
This approach likely has flaws, and I’m unsure how to handle it properly.

I welcome all feedback, discussions and corrections.

Attached are the execution plans for idx_t1_v and idx_t1_s：

```SQL
xman5=# explain analyze select a from t1 where s4 < 100;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=4.14..7.92 rows=2 width=4) (actual time=0.074..0.076 rows=2.00 loops=1)
   Recheck Cond: (s4 < 100)
   Heap Blocks: exact=1
   Buffers: shared hit=2
   ->  Bitmap Index Scan on idx_t1_s  (cost=0.00..4.14 rows=2 width=0) (actual time=0.019..0.019 rows=2.00 loops=1)
         Index Searches: 1
         Buffers: shared hit=1
 Planning Time: 0.420 ms
 Execution Time: 0.118 ms
(9 rows)

xman5=# explain analyze select a from t1 where v4 < 100;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=4.14..7.93 rows=2 width=4) (actual time=0.061..0.063 rows=2.00 loops=1)
   Recheck Cond: ((a * 44) < 100)
   Heap Blocks: exact=1
   Buffers: shared hit=2
   ->  Bitmap Index Scan on idx_t1_v  (cost=0.00..4.14 rows=2 width=0) (actual time=0.019..0.020 rows=2.00 loops=1)
         Index Searches: 1
         Buffers: shared hit=1
 Planning Time: 0.364 ms
 Execution Time: 0.126 ms
(9 rows)

```SQL

regards,
--
ZizhuanLiu (X-MAN) 
[email-masked]

---

From: "ZizhuanLiu X-MAN" <[email-masked]>
Date: Tue, 7 Jul 2026 21:40:10 +0800

Hi everyone,

This is the V4 patch. On top of prior revisions, I've revised generated_virtual.sql
together with its matching output file generated_virtual.out.

The modifications target index constraints, regular indexes, and foreign keys.

I have uncommented the existing test SQL for these modules and added a few
extra test cases.  All tests are passing successfully.

regards,
--
ZizhuanLiu (X-MAN) 
[email-masked]

---

From: "ZizhuanLiu X-MAN" <[email-masked]>
Date: Wed, 8 Jul 2026 18:02:40 +0800

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"

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-masked]