Re: support create index on virtual generated column.

>Original
>From: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
>Date: 2026-07-05 11:04
>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.
>
>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) 
44973863(at)qq(dot)com