Re: support create index on virtual generated column.

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