Re: Optimize MCV stats for sortable types and utilize sorted-order properties

I write:
>Original
>From: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
>Date: 2026-10-18 21:20
>To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
>Cc: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>, tgl <tgl(at)sss(dot)pgh(dot)pa(dot)us>, tomas <tomas(at)vondra(dot)me>, dean.a.rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, guofenglinux <guofenglinux(at)gmail(dot)com>
>Subject: Re: Optimize MCV stats for sortable types and utilize sorted-order properties
>
>
>I write:
>>Original
>>From: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
>>Date: 2026-10-17 20:06
>>To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
>>Cc: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>, tgl <tgl(at)sss(dot)pgh(dot)pa(dot)us>, tomas <tomas(at)vondra(dot)me>, dean.a.rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, guofenglinux <guofenglinux(at)gmail(dot)com>
>>Subject: Re: Optimize MCV stats for sortable types and utilize sorted-order properties
>>......
>>The results are as follows:
>> - var_eq_const(): The patch version is, on average, 43.88% faster than master.
>> - mcv_selectivity(): The patch version is, on average, 39.02% faster than master.
>>......
>
>
>Hi,
>
>Using the same testing methodology, I also tested eqjoinsel(), mergejoinscansel(), and EXPLAIN planning time.
>
>The results are summarized below.
>- Execution plans: Master and the patch produce identical plans for the tested SQL cases.
> See the 02-stats_data_explain sheet in the attached XLS file.
>- Performance: The patch shows substantial improvements in both eqjoinsel() and mergejoinscansel(),
> as well as in planning time. See the 03-Raw_test_data_analyze and 03-test_data_analyze_2 sheets.
>
>Some measurements for master were anomalous. To make the comparison fairer, 03-test_data_analyze_2 excludes
>those anomalous master measurements and recalculates the results using the remaining measurements and the
>same methodology for both master and the patch. The results from both data sets are shown below.
>
>Metric 03-Raw_test_data_analyze 03-test_data_analyze_2
>eqjoinsel() 96.46% 95.08%
>mergejoinscansel() 80.23% 74.17%
>EXPLAIN planning time 91.90% 89.05%

Additional Performance Test Results for Common Data Types

Hi hackers,

I've conducted additional performance tests to evaluate the impact of this patch on common data types and operations.

1. Test methodology
The previous tests primarily used long text strings (over 1,000 characters) with a case_insensitive collation,
which is a relatively specialized scenario.

The new tests cover common data types, including int4 and text, and the following operations:
- VAR vs. CONST: =, <, <=, >, >=
- JOIN: scenarios involving eqjoinsel() and mergejoinscansel()

Each test was run 104 times. The first two runs were discarded as warm-up runs, and the arithmetic mean of the remaining 102 runs was used.
All execution times below are in milliseconds (ms). The time reduction percentage is calculated relative to master.

2. Test results
For the test cases listed below, master and the patch produce the same execution plan paths and estimated row counts.

2.1 var_eq_const()
Test SQL:
EXPLAIN (SUMMARY, COSTS OFF) SELECT * FROM int4_n100_uniform WHERE id = 51;

master (ms) patch (ms) Time reduction (%)
var_eq_const() 0.027 0.011 60.77%
Planning time 0.385 0.203 47.22%

2.2 mcv_selectivity()
Test SQL:
EXPLAIN (SUMMARY, COSTS OFF) SELECT * FROM int4_n100_uniform WHERE id < 51;

master (ms) patch (ms) Time reduction (%)
mcv_selectivity() 0.024 0.010 59.01%
Planning time 0.368 0.171 53.51%

2.3 JOIN: int4
Test SQL:
EXPLAIN (SUMMARY, COSTS OFF) SELECT * FROM int4_n100_uniform WHERE id IN (SELECT id FROM int4_n100_uniform);

master (ms) patch (ms) Time reduction (%)
eqjoinsel() 0.2505 0.0399 84.07%
mergejoinscansel() 0.34941 0.20912 40.15%
Planning time 1.61837 0.77411 52.17%
Combined share of the
two functions (%) 37.07% 32.17% -

2.4 JOIN: text
Test SQL:
EXPLAIN (SUMMARY, COSTS OFF)SELECT * FROM text_n100_uniform
WHERE id IN (SELECT id FROM text_n100_uniform);

master (ms) patch (ms) Time reduction (%)
eqjoinsel() 0.38018 0.04035 89.39%
mergejoinscansel() 0.61253 0.18803 69.30%
Planning time 2.04057 0.66537 67.39%
Combined share of the
two functions (%) 48.65% 34.32% -

3. Initial observations
These results indicate that, in the common data types and query scenarios covered by these tests,
the patch reduces not only the execution time of the relevant selectivity estimation functions, but
also the overall planning time.

In the JOIN tests, both eqjoinsel() and mergejoinscansel() show lower execution times, accompanied
by substantial reductions in overall planning time.

In the earlier tests using long text strings with a case_insensitive collation, the combined share of eqjoinsel()
and mergejoinscansel() in the overall planning time decreased from 93.60% to 59.19%. This is currently the
scenario with the largest observed reduction in overall planning time.

The additional tests suggest that the improvements are not limited to that specialized scenario. Reductions
in overall planning time were also observed with the common int4 and text test cases.

These are the current test results and preliminary observations. Feedback on the test methodology, interpretation
of the results, and other scenarios worth testing would be welcome.

Thanks!

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