nocfbot-0001-add-positive-test-for-metapage-discount.txt

From a98e41ab58b38b4d49d24b45753032582f08b6ae Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
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