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

I write
>From: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
>Date: 2026-10-17 12:18
>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
>
>Hi,
>
>I have found that adding C code to measure the execution time of the specific functions
>being modified provides a more reliable way to assess their performance than measuring
>the execution time of SQL commands.
>
>The execution time of each modified function is relatively small, and measuring it indirectly
>through SQL commands introduces noise from other functions and operations involved in
>executing those commands. This makes it difficult to isolate the performance impact of our
>changes. I also ran each test more than 100 times to obtain more consistent and reliable results.
>
>As a first step, I added C code to three functions to make their individual performance impacts more observable:
>- compute_scalar_stats(): generates STATISTIC_KIND_MCV_VALUE_SORTED (kind 8) for sortable data types.
>- var_eq_const(): handles =.
>- mcv_selectivity(): handles >, >=, <, and <=.
>
>Based on the test results:
>- compute_scalar_stats():
>The patch version is, on average, ***3.2%*** slower than master. This result surprised me, since the patch does not
>introduce any expensive sorting or value-comparison operations in this function. I would have expected the
>performance impact to be negligible, so I am still investigating the cause.
>- var_eq_const(): The patch version is, on average, ***42.19%*** faster than master. This result was quite surprising
>and encouraging.
>- mcv_selectivity(): The patch version is, on average, ***20.62%*** faster than master. This result was also encouraging,
>although the improvement is smaller than that for var_eq_const(). On closer inspection, I found that <= and >= fall back
>to the original full MCV scan because get_ordering_op_properties() does not provide the comparison strategy for these
>operators in the tested cases. Consequently, their performance is almost unchanged compared with master. I am still
>investigating how best to address this.

Hi,

I have replaced get_ordering_op_properties() with get_oprrest(), as used in V5.
The latter is already used by scalararraysel() in the same file.

The following restriction estimator functions are defined in fmgroids.h:
#define F_SCALARLTSEL 103
#define F_SCALARGTSEL 104
#define F_SCALARLESEL 336
#define F_SCALARGESEL 337

In src/backend/utils/adt/selfuncs.c, the relevant call chain is as follows:

scalarltsel() -- Selectivity of "<" for scalars.
scalarlesel() -- Selectivity of "<=" for scalars.
scalargtsel() -- Selectivity of ">" for scalars.
scalargesel() -- Selectivity of ">=" for scalars.
--> scalarineqsel_wrapper(PG_FUNCTION_ARGS, bool isgt, bool iseq)
--> scalarineqsel() -- Selectivity of "<", "<=", ">", and ">=" for scalars.
--> mcv_selectivity()

After switching to get_oprrest(), I reran the performance tests for var_eq_const() and mcv_selectivity()
to compare the patch against master.

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.

Based on the test results:
In master, the execution times of var_eq_const() and mcv_selectivity() are both relatively stable at around 0.080 ms.
With the patch, the execution times are generally around 0.040–0.050 ms for both functions.

Please see the attached XLS file for the detailed test results and analysis.

>For reference, I have attached the v14.patch.debug patch, along with the relevant test scripts and test results.
>The patch is based on v14 and adds function-level performance reporting.
>
>The test files are organized as follows:
>- 00-setup.sql: test setup.
>- 01-gen_test_sql_sh: generates the SQL files used for testing.
>
>**For comparing estimated row counts in execution plans:**
>- 02-gen_master_explain_var_eq_cosnt_mcv_selectivity_sh
>- 03-gen_patch_explain_var_eq_cosnt_mcv_selectivity_sh
>- 06-compare_patch_explain_var_eq_cosnt_mcv_selectivity_sh
>
>**For performance testing:**
>- compute_scalar_stats():
> cat compute_scalar_stats.sql | psql xman7 | grep -v ANALYZE
>- var_eq_const() and mcv_selectivity():
> - 04-test_master_var_eq_cosnt_mcv_selectivity_sh
> - 05-test_patch_var_eq_cosnt_mcv_selectivity_sh
>
>The aggregated ANALYZE test results are also included in test_result_analyze.xlsx.
>
>I would appreciate any feedback on the testing methodology and the results, particularly on
>the unexpected overhead in compute_scalar_stats() and possible ways to improve the handling
>f <= and >= in mcv_selectivity().
>
>Thanks for your time and any suggestions.

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