>Original >From: ZizhuanLiu X-MAN <[email protected]> >Date: 2026-07-05 11:04 >To: jian he <[email protected]>, Peter Eisentraut ><[email protected]>, pgsql-hackers <[email protected]> >Cc: Corey Huinker <[email protected]>, Tom Lane <[email protected]>, >chengpeng_yan <[email protected]>, guofenglinux ><[email protected]>, 我自己的邮箱 <[email protected]> >Subject: Re: support create index on virtual generated column. > >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)
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 protected]
v3-0001-v2-enhance-index-support-for-virtual-generated-co.patch
Description: Binary data
