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)
[email protected]
v1-0001-Fix-var_eq_const-sum-selectivity-of-all-matching-.patch
Description: Binary data
