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