>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
Hi, everyone 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 Rebase V8 to V9. regards, -- ZizhuanLiu (X-MAN) 44973863(at)qq(dot)com
