| 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 |
| 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 |