>Hi everyone,
>
>While revisiting the discussion for `CF6749 — Disallow whole-row index
>references with virtual generated columns? `
>Based on version V7, I’ve added support for whole-row references within
>partial indexes, attached V8 patch.
>
>There is one point I may have overlooked: the current implementation appears
>to only work for btree indexes.
>
>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)
>
>
>regards,
>--
>ZizhuanLiu (X-MAN)
>[email protected]Hi, everyone Rebase V8 to V9. regards, -- ZizhuanLiu (X-MAN) [email protected]
v9-0001-v9-enhance-index-support-for-virtual-generated-co.patch
Description: Binary data
