Re: Fix var_eq_const: sum selectivity of all matching MCV entries instead of stopping at first match

Hi hackers,

  The patch changes var_eq_const() to perform a full MCV scan under the condition described in the comment:
```C
/*
 * Full MCV scanning is only justified and beneficial when the column
 * statistics use a deterministic collation, while the comparison uses
 * a nondeterministic collation.
 */

In other words, a full MCV scan is performed only when the column statistics use
a deterministic collation and the comparison uses a nondeterministic collation. 
In other cases, the existing behavior is preserved.

I have already performed some tests, and patch v4 is based on correctly estimating
the row count from the available statistics.

Recently, I conducted additional tests to evaluate the performance impact on query plans,
as well as how differences in row-count estimation affect plan selection. The observations so far are as follows.

1. Performance impact of the full MCV scan

For the cases where a full MCV scan is required only under specific conditions, the performance of
the patched version depends primarily on the performance of the comparison function and the
number of MCV values, rather than on the position of the matching constant.

This is because the patched version always scans all MCV values in these cases, resulting in O(N) time complexity. 
In contrast, the unpatched version stops as soon as it finds the first matching value, with an average time complexity
of approximately O(N/2).

As shown in the "scan_mcv performance comparison" sheet in stats.xlsx, with the default STATISTICS = 100, 
the worst-case increase in planning time is very small.

However, with the maximum STATISTICS = 10000, the planning times of the unpatched version when
the matching value is located at positions 1, 5000, and 9999 are 1.201 ms, 4.806 ms, and 9.848 ms, respectively. 
In comparison, the patched version remains relatively stable at around 9.623 ms.

2. Impact of row-count estimation on plan selection and execution performance
2.1 enable_material = on

As shown in "v0&v4-enable_material-on", with the default enable_material = on, 
the execution times of the unpatched and patched versions are largely comparable.

There is, however, a notable difference in plan stability.

Unpatched version:
Because the three tables are estimated to have the same cost, the join order is determined by
the order in which the tables appear in the FROM clause, which makes the selected plan unstable. 
Due to the caching effect of Materialize, the six different join orders have very similar execution times.

Patched version:
The plan selection is much more stable.

2.2 enable_material = off

As shown in "v0-explain-enable_material-off" and "v4-explain-enable_material-off", 
the difference becomes much more significant when enable_material = off.

Unpatched version:
Because the three tables are estimated to have the same cost, the join order again depends on
the order in which the tables appear in the FROM clause, resulting in an unstable plan. 
Without the caching provided by Materialize, the six different join orders show substantially
different execution times, falling into three broad groups:
14011.644 ms
60483.486 ms
86543.769 ms

Patched version:
The plan selection is much more stable, with an overall average execution time of approximately 15657.652 ms. 
This is close to the best-performing plan observed with the unpatched version.

The following section describes the preparation and test procedures used for these two sets of tests.
1. Performance impact of the full MCV scan:
    00-scan_mcv_performance_comparison.txt  --continuous tests on the same join order
    "scan_mcv performance comparison" sheet in stats.xlsx

2. Impact of row-count estimation on plan selection and execution performance:
   01-0-list.txt    --FROM-clause join orders
   01-0-setup.sql   --Environment and Dataset Preparation
   01-1-material-off-model.sql & 01-2-material-off-test.sh
   02-1-material-on-model.sql & 02-2-material-on-test.sh
   stats.xlsx  --Test Results and Analysis

test.tar --All test cases and results

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