>Original >From: ZizhuanLiu X-MAN <[email protected]> >Date: 2026-06-24 19:54 >To: álvaro Herrera <[email protected]>, Matthias van de Meent ><[email protected]> >Cc: Ayush Tiwari <[email protected]>, PostgreSQL Hackers ><[email protected]>, Robert Haas <[email protected]> >Subject: Re: Disallow whole-row index references with virtual generated >columns > > >Hi everyone, > >Thanks a lot for all the patches, tests and insightful discussions so far. > >From the current status, PostgreSQL already permits creating indexes with >whole-row references. >However, there exists inconsistent handling between such indexes and the query >planner during runtime. >As a result, these indexes cannot work as expected, including failures in >index scan, index-only scan and >partial predicate matching. > >Given this situation, I intend to improve the related logic to provide better >index support for >both whole-row references and virtual generated columns. > >Therefore, I am sending this proposal: Support virtual generated columns in >index expressions and predicates. > >regards, >-- >ZizhuanLiu (X-MAN) >[email protected]
Hi everyone, I have implemented a patch to support creation of a partial index using a whole-row expression. Details can be found here: https://commitfest.postgresql.org/patch/5667/ support create index on virtual generated column. https://www.postgresql.org/message-id/flat/CACJufxGao-cypdNhifHAdt8jHfK6-HX=trbovbkgruxw063...@mail.gmail.com I welcome everyone’s thoughts and look forward to your feedback. Below are the test results: -- test creation of a predicate index with a whole-row expression DROP TABLE IF EXISTS test_pg_wholerow; NOTICE: table "test_pg_wholerow" does not exist, skipping CREATE TABLE test_pg_wholerow(a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, c text); CREATE UNIQUE INDEX test_pg_wholerow_pred_idx ON test_pg_wholerow(a) WHERE test_pg_wholerow IS NOT NULL; INSERT INTO test_pg_wholerow(a,c) SELECT NULL, NULL FROM generate_series(1, 1000); INSERT INTO test_pg_wholerow(a,c) SELECT 1, '1'; ANALYZE test_pg_wholerow; SELECT relname,reltuples FROM pg_catalog.pg_class WHERE relname IN ('test_pg_wholerow', 'test_pg_wholerow_pred_idx') ORDER BY OID; relname | reltuples ---------------------------+----------- test_pg_wholerow | 1001 test_pg_wholerow_pred_idx | 1 (2 rows) EXPLAIN (COSTS OFF) SELECT * FROM test_pg_wholerow WHERE test_pg_wholerow IS NOT NULL; QUERY PLAN ---------------------------------------------------------------- Index Scan using test_pg_wholerow_pred_idx on test_pg_wholerow (1 row) EXPLAIN (COSTS OFF) SELECT a FROM test_pg_wholerow WHERE test_pg_wholerow IS NOT NULL; QUERY PLAN --------------------------------------------------------------------- Index Only Scan using test_pg_wholerow_pred_idx on test_pg_wholerow (1 row) EXPLAIN (COSTS OFF) SELECT a,b FROM test_pg_wholerow WHERE test_pg_wholerow IS NOT NULL; QUERY PLAN --------------------------------------------------------------------- Index Only Scan using test_pg_wholerow_pred_idx on test_pg_wholerow (1 row) regress=# INSERT INTO test_pg_wholerow(a,c) SELECT 1, '1'; ERROR: duplicate key value violates unique constraint "test_pg_wholerow_pred_idx" DETAIL: Key (a)=(1) already exists. regress=# regards, ZizhuanLiu (X-MAN) [email protected]
