From a98e41ab58b38b4d49d24b45753032582f08b6ae Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Mon, 9 Mar 2026 13:42:33 +0900 Subject: [PATCH] Add test for partial vs full index selection with metapage discount --- src/test/regress/expected/select.out | 20 ++++++++++++++++++++ src/test/regress/sql/select.sql | 16 ++++++++++++++++ 2 files changed, 36 insertions(+) diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index 7e869b79076..0fe9ee28c35 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -908,6 +908,26 @@ select unique1, unique2 from onek2 (2 rows) RESET enable_indexscan; +-- Verify that a small partial index is preferred over a large full index +-- when both are applicable. Without discounting the metapage from index +-- page count, the partial index cost is overestimated for very small indexes. +CREATE TABLE partial_index_test (id int PRIMARY KEY, status text, + filler text DEFAULT repeat('x', 50)); +INSERT INTO partial_index_test + SELECT i, CASE WHEN i <= 5 THEN 'Canceled' ELSE 'Active' END + FROM generate_series(1, 10000) i; +CREATE INDEX pidx_status_full ON partial_index_test (status); +CREATE INDEX pidx_status_partial ON partial_index_test (status) + WHERE status = 'Canceled'; +ANALYZE partial_index_test; +explain (costs off) + SELECT status FROM partial_index_test WHERE status = 'Canceled'; + QUERY PLAN +----------------------------------------------------------------- + Index Only Scan using pidx_status_partial on partial_index_test +(1 row) + +DROP TABLE partial_index_test; -- -- Test some corner cases that have been known to confuse the planner -- diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql index 771b9869a20..5fb7bb5c637 100644 --- a/src/test/regress/sql/select.sql +++ b/src/test/regress/sql/select.sql @@ -234,6 +234,22 @@ select unique1, unique2 from onek2 where (unique2 = 11 and stringu1 < 'B') or unique1 = 0; RESET enable_indexscan; +-- Verify that a small partial index is preferred over a large full index +-- when both are applicable. Without discounting the metapage from index +-- page count, the partial index cost is overestimated for very small indexes. +CREATE TABLE partial_index_test (id int PRIMARY KEY, status text, + filler text DEFAULT repeat('x', 50)); +INSERT INTO partial_index_test + SELECT i, CASE WHEN i <= 5 THEN 'Canceled' ELSE 'Active' END + FROM generate_series(1, 10000) i; +CREATE INDEX pidx_status_full ON partial_index_test (status); +CREATE INDEX pidx_status_partial ON partial_index_test (status) + WHERE status = 'Canceled'; +ANALYZE partial_index_test; +explain (costs off) + SELECT status FROM partial_index_test WHERE status = 'Canceled'; +DROP TABLE partial_index_test; + -- -- Test some corner cases that have been known to confuse the planner -- -- 2.43.0