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.

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.

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