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]
v8-0001-V8-enhance-index-support-for-virtual-generated-co.patch
Description: Binary data
