hash_based_any_tests.patch
text/x-patch
Filename: hash_based_any_tests.patch
Type: text/x-patch
Part: 1
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
| File | + | − |
|---|---|---|
| src/test/regress/expected/planner_est.out | 209 | 0 |
| src/test/regress/sql/planner_est.sql | 112 | 0 |
diff --git a/src/test/regress/expected/planner_est.out b/src/test/regress/expected/planner_est.out
index b62a47552fa..3ef720908f5 100644
--- a/src/test/regress/expected/planner_est.out
+++ b/src/test/regress/expected/planner_est.out
@@ -210,4 +210,213 @@ false, true, false, true);
-> Result (cost=N..N rows=1 width=N)
(4 rows)
+-- Ensure stable and rich MCV statistics
+SET default_statistics_target = 1000;
+CREATE TABLE t_mcv (a int);
+-- Build ~100 MCV values with uniform distribution
+INSERT INTO t_mcv
+SELECT (i % 100)
+FROM generate_series(1, 20000) s(i);
+ANALYZE t_mcv;
+-- =========================================================
+-- CASE 1: Large ANY list (MCV < ANY) → hash on MCV
+-- =========================================================
+-- 1. Single element
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY(SELECT 1));$$,
+false, true, false, true);
+ explain_mask_costs
+--------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=1912 width=N)
+ Filter: (a = ANY ((InitPlan array_1).col1))
+ InitPlan array_1
+ -> Result (cost=N..N rows=1 width=N)
+(4 rows)
+
+-- 2. Multiple elements (all in MCV)
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY(SELECT i FROM generate_series(1,3) s(i)));$$,
+false, true, false, true);
+ explain_mask_costs
+------------------------------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=1912 width=N)
+ Filter: (a = ANY ((InitPlan array_1).col1))
+ InitPlan array_1
+ -> Function Scan on generate_series s (cost=N..N rows=3 width=N)
+(4 rows)
+
+-- 3. Includes non-MCV values
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[1, 2, 1000]);$$,
+false, true, false, true);
+ explain_mask_costs
+-------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=400 width=N)
+ Filter: (a = ANY ('{1,2,1000}'::integer[]))
+(2 rows)
+
+-- 4. Duplicates
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY(SELECT (i % 3) + 1 FROM generate_series(1,30) s(i)));$$,
+false, true, false, true);
+ explain_mask_costs
+-------------------------------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=1912 width=N)
+ Filter: (a = ANY ((InitPlan array_1).col1))
+ InitPlan array_1
+ -> Function Scan on generate_series s (cost=N..N rows=30 width=N)
+(4 rows)
+
+-- =========================================================
+-- CASE 2: Small ANY list (ANY < MCV) → hash on ANY
+-- =========================================================
+-- 1. Single element
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[10]);$$,
+false, true, false, true);
+ explain_mask_costs
+-------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=200 width=N)
+ Filter: (a = ANY ('{10}'::integer[]))
+(2 rows)
+
+-- 2. Multiple elements (all in MCV)
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[10,20,30]);$$,
+false, true, false, true);
+ explain_mask_costs
+-------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=600 width=N)
+ Filter: (a = ANY ('{10,20,30}'::integer[]))
+(2 rows)
+
+-- 3. Includes non-MCV values
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[10,20,10000]);$$,
+false, true, false, true);
+ explain_mask_costs
+--------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=400 width=N)
+ Filter: (a = ANY ('{10,20,10000}'::integer[]))
+(2 rows)
+
+-- 4. Duplicates
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[10,10,10,20,20]);$$,
+false, true, false, true);
+ explain_mask_costs
+-----------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=1000 width=N)
+ Filter: (a = ANY ('{10,10,10,20,20}'::integer[]))
+(2 rows)
+
+-- =========================================================
+-- CASE 3: Guaranteed large case → stress hash path
+-- =========================================================
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY(SELECT i % 100 FROM generate_series(1,500) s(i)));$$,
+false, true, false, true);
+ explain_mask_costs
+--------------------------------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=1912 width=N)
+ Filter: (a = ANY ((InitPlan array_1).col1))
+ InitPlan array_1
+ -> Function Scan on generate_series s (cost=N..N rows=500 width=N)
+(4 rows)
+
+-- =========================================================
+-- CASE 4: inequality (<> ALL)
+-- =========================================================
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a <> ALL (ARRAY[1,2,3]);$$,
+false, true, false, true);
+ explain_mask_costs
+---------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=19400 width=N)
+ Filter: (a <> ALL ('{1,2,3}'::integer[]))
+(2 rows)
+
+-- =========================================================
+-- CASE 5: mix const + non-const
+-- =========================================================
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[1,2] || ARRAY(SELECT i FROM generate_series(3,5) s(i)));$$,
+false, true, false, true);
+ explain_mask_costs
+------------------------------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=1912 width=N)
+ Filter: (a = ANY (('{1,2}'::integer[] || (InitPlan array_1).col1)))
+ InitPlan array_1
+ -> Function Scan on generate_series s (cost=N..N rows=3 width=N)
+(4 rows)
+
+-- =========================================================
+-- CASE 6: NULL handling
+-- =========================================================
+-- ANY with NULL
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[NULL,1]);$$,
+false, true, false, true);
+ explain_mask_costs
+-------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=200 width=N)
+ Filter: (a = ANY ('{NULL,1}'::integer[]))
+(2 rows)
+
+-- ALL with NULL (should be 0 selectivity)
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ALL (ARRAY[1,NULL]);$$,
+false, true, false, true);
+ explain_mask_costs
+-----------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=1 width=N)
+ Filter: (a = ALL ('{1,NULL}'::integer[]))
+(2 rows)
+
+-- =========================================================
+-- CASE 7: Combined all of them
+-- =========================================================
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[1, 2, 3, 1000, 2000, NULL, 1, 1, 2, (SELECT 4), (SELECT 5), (SELECT 10000), (SELECT 4), (SELECT 4)] || ARRAY( SELECT i % 120 FROM generate_series(1, 500) s(i)));$$,
+false, true, false, true);
+ explain_mask_costs
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=1912 width=N)
+ Filter: (a = ANY ((ARRAY[1, 2, 3, 1000, 2000, NULL::integer, 1, 1, 2, (InitPlan expr_1).col1, (InitPlan expr_2).col1, (InitPlan expr_3).col1, (InitPlan expr_4).col1, (InitPlan expr_5).col1] || (InitPlan array_1).col1)))
+ InitPlan expr_1
+ -> Result (cost=N..N rows=1 width=N)
+ InitPlan expr_2
+ -> Result (cost=N..N rows=1 width=N)
+ InitPlan expr_3
+ -> Result (cost=N..N rows=1 width=N)
+ InitPlan expr_4
+ -> Result (cost=N..N rows=1 width=N)
+ InitPlan expr_5
+ -> Result (cost=N..N rows=1 width=N)
+ InitPlan array_1
+ -> Function Scan on generate_series s (cost=N..N rows=500 width=N)
+(14 rows)
+
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a <> ALL (ARRAY[1, 2, 3, 1000, 2000, NULL, 1, 1, 2, (SELECT 4), (SELECT 5), (SELECT 10000), (SELECT 4), (SELECT 4)] || ARRAY( SELECT i % 120 FROM generate_series(1, 500) s(i)));$$,
+false, true, false, true);
+ explain_mask_costs
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on t_mcv (cost=N..N rows=18088 width=N)
+ Filter: (a <> ALL ((ARRAY[1, 2, 3, 1000, 2000, NULL::integer, 1, 1, 2, (InitPlan expr_1).col1, (InitPlan expr_2).col1, (InitPlan expr_3).col1, (InitPlan expr_4).col1, (InitPlan expr_5).col1] || (InitPlan array_1).col1)))
+ InitPlan expr_1
+ -> Result (cost=N..N rows=1 width=N)
+ InitPlan expr_2
+ -> Result (cost=N..N rows=1 width=N)
+ InitPlan expr_3
+ -> Result (cost=N..N rows=1 width=N)
+ InitPlan expr_4
+ -> Result (cost=N..N rows=1 width=N)
+ InitPlan expr_5
+ -> Result (cost=N..N rows=1 width=N)
+ InitPlan array_1
+ -> Function Scan on generate_series s (cost=N..N rows=500 width=N)
+(14 rows)
+
+DROP TABLE t_mcv;
DROP FUNCTION explain_mask_costs(text, bool, bool, bool, bool);
diff --git a/src/test/regress/sql/planner_est.sql b/src/test/regress/sql/planner_est.sql
index 53210d5baad..ba8f8bd8fb6 100644
--- a/src/test/regress/sql/planner_est.sql
+++ b/src/test/regress/sql/planner_est.sql
@@ -147,4 +147,116 @@ SELECT explain_mask_costs($$
SELECT * FROM tenk1 WHERE unique1 <> ALL (ARRAY[1, 2, 98, (SELECT 99), NULL]);$$,
false, true, false, true);
+-- Ensure stable and rich MCV statistics
+SET default_statistics_target = 1000;
+
+CREATE TABLE t_mcv (a int);
+
+-- Build ~100 MCV values with uniform distribution
+INSERT INTO t_mcv
+SELECT (i % 100)
+FROM generate_series(1, 20000) s(i);
+
+ANALYZE t_mcv;
+
+-- =========================================================
+-- CASE 1: Large ANY list (MCV < ANY) → hash on MCV
+-- =========================================================
+
+-- 1. Single element
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY(SELECT 1));$$,
+false, true, false, true);
+
+-- 2. Multiple elements (all in MCV)
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY(SELECT i FROM generate_series(1,3) s(i)));$$,
+false, true, false, true);
+
+-- 3. Includes non-MCV values
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[1, 2, 1000]);$$,
+false, true, false, true);
+
+-- 4. Duplicates
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY(SELECT (i % 3) + 1 FROM generate_series(1,30) s(i)));$$,
+false, true, false, true);
+
+-- =========================================================
+-- CASE 2: Small ANY list (ANY < MCV) → hash on ANY
+-- =========================================================
+
+-- 1. Single element
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[10]);$$,
+false, true, false, true);
+
+-- 2. Multiple elements (all in MCV)
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[10,20,30]);$$,
+false, true, false, true);
+
+-- 3. Includes non-MCV values
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[10,20,10000]);$$,
+false, true, false, true);
+
+-- 4. Duplicates
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[10,10,10,20,20]);$$,
+false, true, false, true);
+
+-- =========================================================
+-- CASE 3: Guaranteed large case → stress hash path
+-- =========================================================
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY(SELECT i % 100 FROM generate_series(1,500) s(i)));$$,
+false, true, false, true);
+
+-- =========================================================
+-- CASE 4: inequality (<> ALL)
+-- =========================================================
+
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a <> ALL (ARRAY[1,2,3]);$$,
+false, true, false, true);
+
+-- =========================================================
+-- CASE 5: mix const + non-const
+-- =========================================================
+
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[1,2] || ARRAY(SELECT i FROM generate_series(3,5) s(i)));$$,
+false, true, false, true);
+
+-- =========================================================
+-- CASE 6: NULL handling
+-- =========================================================
+
+-- ANY with NULL
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[NULL,1]);$$,
+false, true, false, true);
+
+-- ALL with NULL (should be 0 selectivity)
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ALL (ARRAY[1,NULL]);$$,
+false, true, false, true);
+
+-- =========================================================
+-- CASE 7: Combined all of them
+-- =========================================================
+
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a = ANY (ARRAY[1, 2, 3, 1000, 2000, NULL, 1, 1, 2, (SELECT 4), (SELECT 5), (SELECT 10000), (SELECT 4), (SELECT 4)] || ARRAY( SELECT i % 120 FROM generate_series(1, 500) s(i)));$$,
+false, true, false, true);
+
+SELECT explain_mask_costs($$
+SELECT * FROM t_mcv WHERE a <> ALL (ARRAY[1, 2, 3, 1000, 2000, NULL, 1, 1, 2, (SELECT 4), (SELECT 5), (SELECT 10000), (SELECT 4), (SELECT 4)] || ARRAY( SELECT i % 120 FROM generate_series(1, 500) s(i)));$$,
+false, true, false, true);
+
+DROP TABLE t_mcv;
+
+
DROP FUNCTION explain_mask_costs(text, bool, bool, bool, bool);