Re: support create index on virtual generated column.

From: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
To: jian he <jian(dot)universality(at)gmail(dot)com>, Peter Eisentraut <peter(at)eisentraut(dot)org>, pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Corey Huinker <corey(dot)huinker(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, chengpeng_yan <chengpeng_yan(at)outlook(dot)com>, guofenglinux <guofenglinux(at)gmail(dot)com>, 我自己的邮箱 <44973863(at)qq(dot)com>
Subject: Re: support create index on virtual generated column.
Date: 2026-07-08 10:02:40
Message-ID: tencent_B9578D7FF0EB6DC7B408132EDFD9960DD408@qq.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)qq(dot)com

Attachment Content-Type Size
v5-0001-v5-enhance-index-support-for-virtual-generated-co.patch application/octet-stream 54.0 KB
v2-相关成员、函数的整体分析.xlsx application/octet-stream 47.5 KB
v2-Comprehensive Analysis of Related Members and Functions.xlsx application/octet-stream 47.4 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Hayato Kuroda (Fujitsu) 2026-07-08 10:07:03 RE: [PATCH] Preserve replication origin OIDs in pg_upgrade
Previous Message Amit Kapila 2026-07-08 10:02:35 Re: [PATCH] Preserve replication origin OIDs in pg_upgrade