Thread
Commits
-
Virtual generated columns
- 83ea6c54025b 18.0 cited
-
support create index on virtual generated column.
jian he <jian.universality@gmail.com> — 2025-03-26T07:15:00Z
hi. attached patch for implementing $subject feature. * internally such index will be transformed into expression index. for example, an index on (b int GENERATED ALWAYS AS (a * 2) VIRTUAL) will be converted into an expression index on ((a * 2)). * in pageinspect module, add some test to check the index content of virtual generated column. * primary key, unique index over virtual generated column are not supported. not sure they make sense or not. * expression index and predicate index over virtual generated columns are currently not supported. * virtual generated column can not be in "include column" * all types of indexes are supported, and a hash index, gist test has been added. * To support ALTER TABLE SET EXPRESSION, in pg_index, we need to track the original virtual generated column attribute number, so ALTER TABLE SET EXPRESSION can identify which index needs to be rebuilt. * ALTER COLUMN SET DATA TYPE will also cause table rewrite, so we really need to track the virtual generated column attribute number that index was built on.
-
Re: support create index on virtual generated column.
Kirill Reshke <reshkekirill@gmail.com> — 2025-03-26T09:36:39Z
On Wed, 26 Mar 2025 at 12:15, jian he <jian.universality@gmail.com> wrote: > > hi. > attached patch for implementing $subject feature. > > * internally such index will be transformed into expression index. > for example, an index on (b int GENERATED ALWAYS AS (a * 2) VIRTUAL) will be > converted into an expression index on ((a * 2)). > * in pageinspect module, add some test to check the index content of > virtual generated column. > * primary key, unique index over virtual generated column are not supported. > not sure they make sense or not. > * expression index and predicate index over virtual generated columns are > currently not supported. > * virtual generated column can not be in "include column" > * all types of indexes are supported, and a hash index, gist test has > been added. > * To support ALTER TABLE SET EXPRESSION, in pg_index, we need to track > the original > virtual generated column attribute number, so ALTER TABLE SET EXPRESSION can > identify which index needs to be rebuilt. > * ALTER COLUMN SET DATA TYPE will also cause table rewrite, so we really > need to track the virtual generated column attribute number that > index was built on. Hi! patch applies with warns ``` Applying: support create index on virtual generated column. .git/rebase-apply/patch:250: trailing whitespace. * updated correctly, and they don't seem useful anyway. .git/rebase-apply/patch:271: trailing whitespace. * Also check for system used in expressions or predicates. warning: 2 lines add whitespace errors. ``` consider this case: ``` reshke=# CREATE TABLE xx (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL) ; CREATE TABLE reshke=# create index on xx (b); CREATE INDEX reshke=# reshke=# \d+ xx Table "public.xx" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+---------+-----------+----------+-----------------------------+---------+-------------+--------------+------------- a | integer | | | | plain | | | b | integer | | | generated always as (a * 2) | plain | | | Indexes: "xx_b_idx" btree (b) Access method: heap reshke=# alter table xx drop column b; ALTER TABLE reshke=# \d+ xx Table "public.xx" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+---------+-----------+----------+---------+---------+-------------+--------------+------------- a | integer | | | | plain | | | Indexes: "xx_b_idx" btree ("........pg.dropped.2........" int4_ops) Access method: heap reshke=# ``` with regular columns we have different behaviour - with drop column we drop the index -- Best regards, Kirill Reshke -
Re: support create index on virtual generated column.
jian he <jian.universality@gmail.com> — 2025-04-14T11:09:45Z
On Wed, Mar 26, 2025 at 5:36 PM Kirill Reshke <reshkekirill@gmail.com> wrote: > reshke=# CREATE TABLE xx (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL) ; > CREATE TABLE > reshke=# create index on xx (b); > CREATE INDEX > reshke=# > reshke=# \d+ xx > Table "public.xx" > Column | Type | Collation | Nullable | Default > | Storage | Compression | Stats target | Description > --------+---------+-----------+----------+-----------------------------+---------+-------------+--------------+------------- > a | integer | | | > | plain | | | > b | integer | | | generated always as (a * 2) > | plain | | | > Indexes: > "xx_b_idx" btree (b) > Access method: heap > > reshke=# alter table xx drop column b; > ALTER TABLE > reshke=# \d+ xx > Table "public.xx" > Column | Type | Collation | Nullable | Default | Storage | > Compression | Stats target | Description > --------+---------+-----------+----------+---------+---------+-------------+--------------+------------- > a | integer | | | | plain | > | | > Indexes: > "xx_b_idx" btree ("........pg.dropped.2........" int4_ops) > Access method: heap > > reshke=# > ``` > > with regular columns we have different behaviour - with drop column we > drop the index > I was wrong about dependency. when creating an index on a virtual generated column, it will have dependency with virtual generated column attribute and the generation expression associated attribute. new patch attached. Now, ALTER TABLE DROP COLUMN works fine. ALTER INDEX ATTACH PARTITION works fine. creating such an index on a partitioned table works just fine. for table inheritance: create index on parent table will not cascade to child table, so we don't need to worry about this. -
Re: support create index on virtual generated column.
Kirill Reshke <reshkekirill@gmail.com> — 2025-04-14T12:05:25Z
On Mon, 14 Apr 2025 at 16:10, jian he <jian.universality@gmail.com> wrote: > > new patch attached. Now, > ALTER TABLE DROP COLUMN works fine. > ALTER INDEX ATTACH PARTITION works fine. > creating such an index on a partitioned table works just fine. > for table inheritance: create index on parent table will not cascade > to child table, > so we don't need to worry about this. Hi! I reviewed v2, and it seems to be working now. But there are tests that are comment-out, what is their purpose? I note that commit 83ea6c5 also included some commented tests, so perhaps there's a reason I'm not aware of. ``` ALTER TABLE gtest22c DROP COLUMN e; \d gtest22c -- EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6; -- SELECT * FROM gtest22c WHERE b * 3 = 6; -- EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0; -- SELECT * FROM gtest22c WHERE a = 1 AND b > 0; ``` -- Best regards, Kirill Reshke
-
Re: support create index on virtual generated column.
jian he <jian.universality@gmail.com> — 2025-04-15T08:36:46Z
On Mon, Apr 14, 2025 at 8:05 PM Kirill Reshke <reshkekirill@gmail.com> wrote: > > On Mon, 14 Apr 2025 at 16:10, jian he <jian.universality@gmail.com> wrote: > > > > new patch attached. Now, > > ALTER TABLE DROP COLUMN works fine. > > ALTER INDEX ATTACH PARTITION works fine. > > creating such an index on a partitioned table works just fine. > > for table inheritance: create index on parent table will not cascade > > to child table, > > so we don't need to worry about this. > > Hi! I reviewed v2, and it seems to be working now. > > But there are tests that are comment-out, what is their purpose? I > note that commit 83ea6c5 also included some commented tests, so > perhaps there's a reason I'm not aware of. > > ``` > ALTER TABLE gtest22c DROP COLUMN e; > \d gtest22c > > -- EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6; > -- SELECT * FROM gtest22c WHERE b * 3 = 6; > -- EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0; > -- SELECT * FROM gtest22c WHERE a = 1 AND b > 0; > ``` comment out tests are for to be implemented feature. There are some test changes that are indeed not necessary, I restored it back, please check attached.
-
Re: support create index on virtual generated column.
jian he <jian.universality@gmail.com> — 2025-07-08T06:37:37Z
On Tue, Apr 15, 2025 at 4:36 PM jian he <jian.universality@gmail.com> wrote: > > comment out tests are for to be implemented feature. > There are some test changes that are indeed not necessary, I restored it back, > please check attached. hi. refactor and rebase.
-
Re: support create index on virtual generated column.
jian he <jian.universality@gmail.com> — 2025-07-10T03:47:51Z
On Tue, Jul 8, 2025 at 2:37 PM jian he <jian.universality@gmail.com> wrote: > > On Tue, Apr 15, 2025 at 4:36 PM jian he <jian.universality@gmail.com> wrote: > > > > comment out tests are for to be implemented feature. > > There are some test changes that are indeed not necessary, I restored it back, > > please check attached. > > hi. > refactor and rebase. fix the regress tests failure in v4.
-
Re: support create index on virtual generated column.
Corey Huinker <corey.huinker@gmail.com> — 2025-07-22T20:44:42Z
> > > hi. > > refactor and rebase. > > fix the regress tests failure in v4. > This may need another rebase, as it doesn't apply to master. I'm interested in this feature, specifically whether the optimizer uses the index in situations where the expression is used rather than the virtual column name. For example: CREATE TABLE example ( regular_name text, lowecase_name text GENERATED ALWAYS AS lower(regular_name) VIRTUAL ); CREATE INDEX example_b ON example(b); EXPLAIN SELECT regular_name FROM example WHERE lowercase_name = 'john q smith'; EXPLAIN SELECT regular_name FROM example WHERE lower(regular_name) = 'john q smith';
-
Re: support create index on virtual generated column.
Tom Lane <tgl@sss.pgh.pa.us> — 2025-07-22T20:54:45Z
Corey Huinker <corey.huinker@gmail.com> writes: > I'm interested in this feature, specifically whether the optimizer uses the > index in situations where the expression is used rather than the virtual > column name. Hmm, I kinda think we should not do this. The entire point of a virtual column is that its values are not stored and so you can (for example) change the generation expression "for free". If it's referenced in an index that advantage goes out the window because we'll have to rebuild the index. Besides, this does nothing you haven't been able to do for decades with expression indexes. regards, tom lane
-
Re: support create index on virtual generated column.
jian he <jian.universality@gmail.com> — 2025-07-24T10:46:39Z
On Wed, Jul 23, 2025 at 4:54 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Corey Huinker <corey.huinker@gmail.com> writes: > > I'm interested in this feature, specifically whether the optimizer uses the > > index in situations where the expression is used rather than the virtual > > column name. > > Hmm, I kinda think we should not do this. The entire point of a > virtual column is that its values are not stored and so you can > (for example) change the generation expression "for free". > If it's referenced in an index that advantage goes out the window > because we'll have to rebuild the index. > > Besides, this does nothing you haven't been able to do for > decades with expression indexes. > hi. CREATE TABLE example (regular_name text, lowecase_name text GENERATED ALWAYS AS (lower(regular_name)) VIRTUAL); CREATE INDEX example_b ON example(lowecase_name); CREATE INDEX example_c ON example(lower(regular_name)); select distinct indnatts,indnkeyatts,indisunique, indnullsnotdistinct,indisprimary,indisexclusion,indimmediate,indisclustered,indisvalid, indcheckxmin,indisready,indislive,indisreplident,indkey,indcollation,indclass,indoption, indexprs from pg_index where indrelid ='example'::regclass; will return one row, meaning catalog table pg_index stored almost all the same information. For indexes example_b and example_c, the only difference lies in the new column indattrgenerated. In example_b, indattrgenerated is not null, whereas in example_c, it is null. This column (indattrgenerated) is needed to track dependencies on generated columns, which is important for index rebuild. obviously, get_relation_info will collect the same information for example_b, example_c. which means the optimizer will use the same information to make the decision. --------------------------------- set enable_seqscan to off; set enable_bitmapscan to off; CREATE TABLE example (regular_name text, lowecase_name text GENERATED ALWAYS AS (lower(regular_name)) VIRTUAL); CREATE INDEX example_b ON example(lowecase_name); EXPLAIN(COSTS OFF) SELECT regular_name FROM example WHERE lowecase_name = 'john q smith'; EXPLAIN(COSTS OFF) SELECT regular_name FROM example WHERE lower(regular_name) = 'john q smith'; So current implementation, the above two query plans will produce the same query plan. the generation expression or virtual generated column data type changes will cause the index to rebuild. Is this we want? Or should changing the generation expression or data type of a virtual generated column mark the associated index as invalid, without triggering a rebuild?
-
Re: support create index on virtual generated column.
Corey Huinker <corey.huinker@gmail.com> — 2025-07-30T18:24:25Z
On Tue, Jul 22, 2025 at 4:54 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Corey Huinker <corey.huinker@gmail.com> writes: > > I'm interested in this feature, specifically whether the optimizer uses > the > > index in situations where the expression is used rather than the virtual > > column name. > > Hmm, I kinda think we should not do this. The entire point of a > virtual column is that its values are not stored and so you can > (for example) change the generation expression "for free". > If it's referenced in an index that advantage goes out the window > because we'll have to rebuild the index. > Don't we already have dependencies on altering a column if it's indexed? Wouldn't that be all the barrier we'd need? > > Besides, this does nothing you haven't been able to do for > decades with expression indexes. > What I'm hoping for with this feature is a smoother transition when people start introducing virtual columns. If there was already an index on that expression, and some queries start using the new virtual column (with the same expression), will that cause those queries to miss the index we already have? If it doesn't, then a customer can roll out the query changes at will and not need to do some cut-over from using the expression to using the virtual column.
-
Re: support create index on virtual generated column.
jian he <jian.universality@gmail.com> — 2026-01-08T06:16:49Z
On Thu, Jul 31, 2025 at 2:24 AM Corey Huinker <corey.huinker@gmail.com> wrote: > >> >> Besides, this does nothing you haven't been able to do for >> decades with expression indexes. > > > What I'm hoping for with this feature is a smoother transition when people start introducing virtual columns. If there was already an index on that expression, and some queries start using the new virtual column (with the same expression), will that cause those queries to miss the index we already have? If it doesn't, then a customer can roll out the query changes at will and not need to do some cut-over from using the expression to using the virtual column. > hi. I am not sure whether this concern has been addressed, as I am still somewhat confused by the above paragraph. As noted earlier, creating an index on a virtual generated column results in a new index, and that index behaves the same as a regular expression index. An updated and polished patch is attached. The regress tests are quite verbose at the moment, since I make it covered all index types (btree, gist, spgist, hash, gin, and brin). -- jian https://www.enterprisedb.com/
-
Re: support create index on virtual generated column.
Soumya S Murali <soumyamurali.work@gmail.com> — 2026-02-27T10:45:33Z
Hi all, On Thu, Feb 19, 2026 at 5:18 PM jian he <jian.universality@gmail.com> wrote: > > On Thu, Jul 31, 2025 at 2:24 AM Corey Huinker <corey.huinker@gmail.com> wrote: > > > >> > >> Besides, this does nothing you haven't been able to do for > >> decades with expression indexes. > > > > > > What I'm hoping for with this feature is a smoother transition when people start introducing virtual columns. If there was already an index on that expression, and some queries start using the new virtual column (with the same expression), will that cause those queries to miss the index we already have? If it doesn't, then a customer can roll out the query changes at will and not need to do some cut-over from using the expression to using the virtual column. > > > > hi. > I am not sure whether this concern has been addressed, as I am still somewhat > confused by the above paragraph. > > As noted earlier, creating an index on a virtual generated column results in a > new index, and that index behaves the same as a regular expression index. > > An updated and polished patch is attached. The regress tests are quite verbose > at the moment, since I make it covered all index types (btree, gist, spgist, > hash, gin, and brin). > I went through the discussions and have reviewed the patch. During testing, I encountered a segmentation fault during initdb which occurred due to a crash in bootstrap mode when ComputeIndexAttrs() was invoked with pstate == NULL while system catalog indexes were being created. The virtual generated column handling logic was executing unconditionally, which is unsafe during bootstrap because catalog and syscache state are not fully initialized. I fixed this by guarding the virtual generated column logic to skip the logic when IsBootstrapProcessingMode() is true so that it can prevent the generated-column rewrite from executing during bootstrap. After the fix, initdb no longer crashes in bootstrap mode, the full regression test suite passes cleanly, the server starts normally and key behaviors like index creation on virtual generated columns, planner rewrite, dependency tracking, partitioned table support, and dump/restore roundtrips are all correct. Also I performed some additional validations to ensure consistent behaviours. With the bootstrap guard in place, the patch seems functionally correct, catalog-safe, and dump/restore safe. Kindly review the patch. Looking forward to more feedback. Regards, Soumya
-
Re: support create index on virtual generated column.
Soumya S Murali <soumyamurali.work@gmail.com> — 2026-03-04T05:43:33Z
Hi all, While validating the bootstrap crash fix for the virtual generated column index patch, I reviewed the current regression coverage around this area. During this process, I noticed that the existing regression suite verifies the functional restrictions on virtual generated columns, but there is no dedicated regression test specifically covering index-related restrictions. As this patch touches index creation logic and also exposes a bootstrap-time crash during testing, it may be useful to add explicit regression coverage for these cases. Having a focused test would help ensure that the current restrictions remain stable and that any future changes affecting this area are detected early by the regression framework and may strengthen long-term test coverage. As a small enhancement in this direction, I prepared a regression test that verifies the expected errors for the following scenarios: 1. CREATE INDEX on a virtual generated column 2. Partial index on a virtual generated column 3. PRIMARY KEY on a virtual generated column 4. Expression index referencing a virtual generated column This ensures the current restrictions remain protected by the regression framework and helps detect unintended changes in the future. The attached patch only adds the regression test and expected output. If this finds useful, kindly verify the patch attached herewith and please let me know the thoughts on this. I would be happy to refine or extend the test further. Looking forward to more feedback. Regards, Soumya
-
Re: support create index on virtual generated column.
Peter Eisentraut <peter@eisentraut.org> — 2026-03-13T14:01:55Z
On 08.01.26 07:16, jian he wrote: > On Thu, Jul 31, 2025 at 2:24 AM Corey Huinker <corey.huinker@gmail.com> wrote: >> >>> >>> Besides, this does nothing you haven't been able to do for >>> decades with expression indexes. >> >> >> What I'm hoping for with this feature is a smoother transition when people start introducing virtual columns. If there was already an index on that expression, and some queries start using the new virtual column (with the same expression), will that cause those queries to miss the index we already have? If it doesn't, then a customer can roll out the query changes at will and not need to do some cut-over from using the expression to using the virtual column. >> > > hi. > I am not sure whether this concern has been addressed, as I am still somewhat > confused by the above paragraph. > > As noted earlier, creating an index on a virtual generated column results in a > new index, and that index behaves the same as a regular expression index. > > An updated and polished patch is attached. The regress tests are quite verbose > at the moment, since I make it covered all index types (btree, gist, spgist, > hash, gin, and brin). I think you could do a much simpler initial version of this if you just supported virtual generated columns in expression indexes. And then prohibit SET EXPRESSION if the column is used in an index. Then you don't need to worry about index rebuilding, ALTER TABLE recursion, new catalog columns, and all that. But there is a comment in DefineIndex(): /* * XXX Virtual generated columns in index expressions or predicates * could be supported, but it needs support in * RelationGetIndexExpressions() and RelationGetIndexPredicate(). */ which you delete, but you don't make any changes to those mentioned functions. Maybe the comment is wrong, in which case, let's discuss that and fix it. (If the comment is indeed wrong, then the feature might even be very easy.) -
Re: support create index on virtual generated column.
jian he <jian.universality@gmail.com> — 2026-03-16T03:31:42Z
On Fri, Mar 13, 2026 at 10:01 PM Peter Eisentraut <peter@eisentraut.org> wrote: > > I think you could do a much simpler initial version of this if you just > supported virtual generated columns in expression indexes. And then > prohibit SET EXPRESSION if the column is used in an index. Then you > don't need to worry about index rebuilding, ALTER TABLE recursion, new > catalog columns, and all that. > CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a) VIRTUAL); CREATE INDEX gtest22c_a_idx ON gtest22c (a); CREATE INDEX gtest22c_b_idx ON gtest22c (b); If we don't add a new catalog column (just a single boolean indisvirtual is not enough, i think), how can we distinguish between the gtest22c_a_idx and gtest22c_b_idx indexes in the example above? If CREATE INDEX simply expands the virtual generated column expression without dependency tracking, that would be quite easy, see the attached v8. If so, we need to explicitly document that SET EXPRESSION has no effect on existing indexes that originally referenced the virtual generated column when CREATE INDEX was used. > But there is a comment in DefineIndex(): > > /* > * XXX Virtual generated columns in index expressions or predicates > * could be supported, but it needs support in > * RelationGetIndexExpressions() and RelationGetIndexPredicate(). > */ > > which you delete, but you don't make any changes to those mentioned > functions. Maybe the comment is wrong, in which case, let's discuss > that and fix it. (If the comment is indeed wrong, then the feature > might even be very easy.) > I don't think it's a good idea to store the virtual generated columns as is in Form_pg_index->indkey because IndexInfo->ii_IndexAttrNumbers and Form->pg_index->indkey are referenced in too many places (see BuildIndexInfo). For every single occurrence of ndkey.values[i], we need to consider whether it's ok for it be a virtual generated column. Instead, Anum_pg_index_indexprs and Anum_pg_index_indpred store the expressions after the virtual generated columns expansion, then we don't need to worry about Form_pg_index->indkey.values[i] is virtual generated column or not. Therefore, i think RelationGetIndexExpressions and RelationGetIndexPredicate don't need to deal with virtual generated column expressions at all. Overall, I think the comment above is wrong. -- jian https://www.enterprisedb.com/
-
Re: support create index on virtual generated column.
Kirill Reshke <reshkekirill@gmail.com> — 2026-05-06T09:20:15Z
That needed rebase since 570e2fcc041a, so PFA. For v8: > stmt->whereClause = > expand_generated_columns_in_expr(stmt->whereClause, rel, 1); Maybe add at least an assertion stmt->whereClause != NULL, like the one we already do in ComputeIndexAttrs? -- Best regards, Kirill Reshke
-
Re: support create index on virtual generated column.
X-MAN <44973863@qq.com> — 2026-07-05T03:04:09Z
>On Fri, Mar 13, 2026 at 10:01?PM Peter Eisentraut <peter@eisentraut.org> wrote: >> >> I think you could do a much simpler initial version of this if you just >> supported virtual generated columns in expression indexes. And then >> prohibit SET EXPRESSION if the column is used in an index. Then you >> don't need to worry about index rebuilding, ALTER TABLE recursion, new >> catalog columns, and all that. >> > >CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a) VIRTUAL); >CREATE INDEX gtest22c_a_idx ON gtest22c (a); >CREATE INDEX gtest22c_b_idx ON gtest22c (b); > >If we don't add a new catalog column (just a single boolean >indisvirtual is not enough, i think), >how can we distinguish between the gtest22c_a_idx and gtest22c_b_idx >indexes in the example above? > >If CREATE INDEX simply expands the virtual generated column expression >without dependency tracking, that would be quite easy, see the >attached v8. >If so, we need to explicitly document that SET EXPRESSION has no >effect on existing indexes that originally referenced the virtual >generated column when CREATE INDEX was used. > >> But there is a comment in DefineIndex(): >> >> /* >> * XXX Virtual generated columns in index expressions or predicates >> * could be supported, but it needs support in >> * RelationGetIndexExpressions() and RelationGetIndexPredicate(). >> */ >> >> which you delete, but you don't make any changes to those mentioned >> functions. Maybe the comment is wrong, in which case, let's discuss >> that and fix it. (If the comment is indeed wrong, then the feature >> might even be very easy.) >> > >I don't think it's a good idea to store the virtual generated columns >as is in Form_pg_index->indkey >because IndexInfo->ii_IndexAttrNumbers and Form->pg_index->indkey are >referenced in too many places (see BuildIndexInfo). >For every single occurrence of ndkey.values[i], we need to consider >whether it's ok for it be a virtual generated column. >Instead, Anum_pg_index_indexprs and Anum_pg_index_indpred store the >expressions after the virtual generated columns expansion, >then we don't need to worry about Form_pg_index->indkey.values[i] is >virtual generated column or not. > >Therefore, i think RelationGetIndexExpressions and >RelationGetIndexPredicate don't need to >deal with virtual generated column expressions at all. >Overall, I think the comment above is wrong. > > > >-- >jian >https://www.enterprisedb.com/ Hi, hackers Quick glossary: VGC = virtual generated columns SGC = stored generated columns ### 1. Iteration of implementation attempts I first drafted V0: no VGC expansion during index creation, only temporary expansion at runtime. The scope of required changes kept expanding (see `v0.patch.txt`), so I abandoned this approach. Next I tried V1, expanding VGCs directly on index creation to support VGC in index expressions, `INCLUDE` columns and partial index `WHERE` clauses. Unfortunately this would break metadata catalog immutability. Before finalizing the patch (see `v1.patch.txt`), I found prior community discussions and prototype attempts with several major design revisions: https://postgr.es/m/CACJufxGao-cypdNhifHAdt8jHfK6-HX=tRBovBkgRuxw063GaA@mail.gmail.com https://postgr.es/m/CACJufxGgkH0PyyqP6ggqcEWHxZzmkV=puY8ad=s8kisss9MAwg@mail.gmail.com https://postgr.es/m/18970-a7d1cfe1f8d5d8d9@postgresql.org https://www.postgresql.org/message-id/flat/CACJufxExe7%2BGh5MKMFiN5xwwmPXaaZC1jjQOFuovv8Q7b1LgFw%40mail.gmail.com#de1541048f0d8257248e7eb9cbb4a440 https://www.postgresql.org/message-id/flat/CACJufxGgkH0PyyqP6ggqcEWHxZzmkV%3DpuY8ad%3Ds8kisss9MAwg%40mail.gmail.com#6fe38ee2053af1ac025b1f8d9261a168 https://commitfest.postgresql.org/patch/5667 https://commitfest.postgresql.org/patch/6094/ https://www.postgresql.org/message-id/flat/CAMbWs4811nG3w_3sLxps%2BEAuUsffA_83ZQ-1acEH_jQeq117mg%40mail.gmail.com I then redesigned for V2. Developing this version made it clear how complex the feature is. Every change was reviewed for local behavior plus upstream/downstream impacts, though some edge cases may still be overlooked. ### 2. Core design principles 1. **Goal**: Enable VGC support for regular index columns, expression columns, `INCLUDE` columns and columns used in partial index predicates. 2. **Immutable metadata catalog**: We must not alter existing catalog entries(see v2-SQL_test_result.xlsx). VGCs are benchmarked against SGCs to guarantee no regressions for index rebuild, logical replication, index comparison, stats collection and `pg_dump`. 3. **Runtime expansion required**: The parser expands VGCs during query analysis, while SGCs are stored unexpanded at index creation. Without runtime expansion, the planner cannot properly select access paths, sort strategies or partial indexes. I added paired expand fields and helper functions to `IndexInfo`, `RelationData` and `IndexOptInfo`. Expanded expressions are built once instead of dynamically on each access, hence paired code paths for raw and expanded fields. 4. I updated comments for `ComputeIndexAttrs()` and `makeIndexInfo()`. These functions remain unmodified; we expand the new fields right after calling them for later access safety. 5. Major changes to `FormIndexDatum`: VGC values are computed on the fly via expanded expressions and stored inindex tuples, behaving like SGCs to support index key evaluation, INCLUDE columns, expression indexes, and partial index matching. ### 3. Main challenge The most tricky part is distinguishing which code paths should use raw non-expanded nodes versus expanded ones, which consumed most of my development effort. ### 4. Code review scope I audited all code paths for the following functions/fields to choose expansion mode and assess side effects: makeIndexInfo|indpredExpand|indpred|indexprsExpand|indexprs|ii_PredicateState|ii_PredicateExpandState| ii_PredicateExpand|ii_Predicate|ii_ExpressionsState|ii_ExpressionsExpandState|ii_ExpressionsExpand|ii_Expressions| RelationGetIndexPredicateExpand |RelationGetIndexPredicate|RelationGetIndexExpressionsExpand| RelationGetIndexExpressions|FormIndexDatum|ComputeIndexAttrs|rd_indpredExpand|rd_indpred|rd_indexprsExpand|rd_indexprs. All changes and behavioral impacts are recorded in v2-相关成员、函数的整体分析.xlsx (Chinese) v2-Comprehensive Analysis of Related Members and Functions.xlsx (English). ### 5. Current status While I have made substantial changes, full test coverage may still be incomplete. Community review, testing and feedback are highly appreciated. Based on current progress and my testing (which is admittedly not comprehensive or thorough), it aligns with SGCs in most metadata catalog aspects. see "v2-SQL_test_result.xlsx" including checking SQL. 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) regards, -- ZizhuanLiu (X-MAN) 44973863@qq.com -
Re: support create index on virtual generated column.
X-MAN <44973863@qq.com> — 2026-07-05T09:00:53Z
I've checked the CFbot test results; failures are mostly limitedto `generated_virtual.sql` and its related test cases. regards, -- ZizhuanLiu (X-MAN) 44973863@qq.com
-
Re: support create index on virtual generated column.
X-MAN <44973863@qq.com> — 2026-07-06T16:09:36Z
>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 -
Re: support create index on virtual generated column.
X-MAN <44973863@qq.com> — 2026-07-07T13:40:10Z
Hi everyone, This is the V4 patch. On top of prior revisions, I've revised generated_virtual.sql together with its matching output file generated_virtual.out. The modifications target index constraints, regular indexes, and foreign keys. I have uncommented the existing test SQL for these modules and added a few extra test cases. All tests are passing successfully. regards, -- ZizhuanLiu (X-MAN) 44973863@qq.com
-
Re: support create index on virtual generated column.
X-MAN <44973863@qq.com> — 2026-07-08T10:02:40Z
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@qq.com