Fix var_eq_const: sum selectivity of all matching MCV entries instead of stopping at first match

From: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: 我自己的邮箱 <44973863(at)qq(dot)com>
Subject: Fix var_eq_const: sum selectivity of all matching MCV entries instead of stopping at first match
Date: 2026-07-30 11:03:42
Message-ID: tencent_A15A9D89A86F2E4B086ABA462578B9B64307@qq.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi, kackers

While reviewing CF6397(https://commitfest.postgresql.org/patch/6397/), I noticed that
the function `var_eq_const()` located at `backend/utils/adt/selfuncs.c` consumes statistical
data from the `most_common_vals` and `most_common_freqs` columns in the system
catalog `pg_catalog.pg_stats`. Currently, the function terminates iteration immediately
after finding the first matching entry and adopts the selectivity of this single matched value.

I believe this estimation logic is inaccurate. Instead, we should traverse all entries in
`most_common_vals`, check for matches against each entry, and sum up the selectivities
of all matching items.

For example, take the predicate `WHERE a = 'b' COLLATE "case_insensitive"` (case-insensitive matching).
The selectivities for both `B` and `b` should be summed to calculate the final overall selectivity.

Below are the test SQL scenarios and corresponding results before and after modification.

### Test Environment Setup
CREATE TABLE test_stats_ext_coll (a text, b text, c int);
-- Insert 500 records
INSERT INTO test_stats_ext_coll SELECT chr(65 + g % 52) FROM generate_series(1, 500) g;
ANALYZE test_stats_ext_coll;

Check column statistics with the following command:
select * from pg_catalog.pg_stats where tablename = 'test_stats_ext_coll'\gx

Key fields extracted:
most_common_vals | {B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,[,"\\",],^,_,`,a,A,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t}
most_common_freqs | {0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018,0.018}

The selectivity of value `B` is 0.02 (corresponding to 10 rows), and the selectivity of `b` is 0.018 (corresponding to 9 rows).
When matched under the `case_insensitive` collation, these two values aggregate to a total of 19 rows:
SELECT a COLLATE "case_insensitive", count(*) FROM test_stats_ext_coll GROUP BY a COLLATE "case_insensitive";
a | count
---+-------
……
D | 19
B | 19
(32 rows)

#### Original Behavior: Inaccurate Row Estimation
The planner estimates only 10 rows, which deviates from the actual count of 19:
explain analyze SELECT * FROM test_stats_ext_coll where a = 'b' COLLATE "case_insensitive";
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on test_stats_ext_coll (cost=0.00..9.25 rows=10 width=38) (actual time=0.181..1.749 rows=19.00 loops=1)
Filter: (a = 'b'::text COLLATE case_insensitive)
Rows Removed by Filter: 481
Buffers: shared hit=3
Planning Time: 1239800.560 ms
Execution Time: 1.830 ms
(6 rows)

#### Revised Behavior: Accurate Estimation After Code Modification & Recompilation
After accumulating selectivities for all matching values, the planner correctly estimates 19 rows:
explain analyze SELECT * FROM test_stats_ext_coll where a = 'b' COLLATE "case_insensitive";
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on test_stats_ext_coll (cost=0.00..9.25 rows=19 width=38) (actual time=1.305..3.066 rows=19.00 loops=1)
Filter: (a = 'b'::text COLLATE case_insensitive)
Rows Removed by Filter: 481
Buffers: shared read=3
Planning:
Buffers: shared hit=21 read=26
Planning Time: 50.362 ms
Execution Time: 3.379 ms
(8 rows)
```

#### Control Cases with Only One Matching Entry
The logic remains correct when only one MCV entry matches the predicate.

Case 1: `a = 'B'`
explain analyze SELECT * FROM test_stats_ext_coll where a = 'B';
Seq Scan on test_stats_ext_coll (cost=0.00..9.25 rows=10 width=38) (actual time=0.270..6.532 rows=10.00 loops=1)
Filter: (a = 'B'::text)
Rows Removed by Filter: 490
Buffers: shared hit=3
Planning Time: 0.625 ms
Execution Time: 6.603 ms
(6 rows)

Case 2: `a = 'A'`
explain analyze SELECT * FROM test_stats_ext_coll where a = 'b';
Seq Scan on test_stats_ext_coll (cost=0.00..9.25 rows=9 width=38) (actual time=0.298..1.790 rows=9.00 loops=1)
Filter: (a = 'b'::text)
Rows Removed by Filter: 491
Buffers: shared hit=3
Planning Time: 0.631 ms
Execution Time: 1.854 ms
(6 rows)

regards,
--
ZizhuanLiu (X-MAN) 
44973863(at)qq(dot)com

Attachment Content-Type Size
v1-0001-Fix-var_eq_const-sum-selectivity-of-all-matching-.patch application/octet-stream 1.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Hannu Krosing 2026-07-30 11:06:39 Re: Direct Toast PoC
Previous Message Daniel Gustafsson 2026-07-30 11:02:39 Re: datachecksums: handle invalid and dropped databases during enable