Re: support create index on virtual generated column.

X-MAN <44973863@qq.com>

From: ZizhuanLiu X-MAN <44973863@qq.com>
To: jian he <jian.universality@gmail.com>, Peter Eisentraut <peter@eisentraut.org>, pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Cc: Corey Huinker <corey.huinker@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, chengpeng_yan <chengpeng_yan@outlook.com>, guofenglinux <guofenglinux@gmail.com>, 我自己的邮箱 <44973863@qq.com>
Date: 2026-07-08T10:02:40Z
Lists: pgsql-hackers

Attachments

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) 
44973863@qq.com

Commits

  1. Virtual generated columns