Re: Disallow whole-row index references with virtual generated columns?

>Original
>From: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
>Date: 2026-06-24 19:54
>To: álvaro Herrera <alvherre(at)kurilemu(dot)de>, Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>
>Cc: Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>
>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)
>44973863(at)qq(dot)com

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=tRBovBkgRuxw063GaA(at)mail(dot)gmail(dot)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)
44973863(at)qq(dot)com