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

Original
>From: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>
>Date: 2026-08-24 21:54
>To: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>, pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
>Cc: Damil Shahzad <shahzaddamil(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>Subject: Re: Fix var_eq_const: sum selectivity of all matching MCV entries instead of stopping at first match
>
>
>--
>Best regards,
>Ilia Evdokimov,
>Tantor Labs LLC,
>https://tantorlabs.com/

Hi Ilia,
Thanks for your feedback and hints.

>Even with statistics_target set to its maximum 10 000, merging the two
>loops would not bring much benefit. So I agree with that from the start
>- my earlier change does not really add much value.

From your stress tests, the patch shows no performance regression, which is good.
If you have the corresponding test SQL and test data, feel free to share them so I can take a look.

>The examples so far only show a single Seq Scan, where the more accurate
>estimate does not change the plan. Could we see a case where the
>corrected MCV sum actually changes the chosen plan rather than just the
>row estimate?

I’ve prepared some tests, see attachment.

In `v0.sql.out` (unpatched), `Seq Scan on public.t_name_2` is underestimated and selected as
NLJ outer table, with `Index Only Scan using idx_t_name_1` as inner table.
In `v4.sql.out` (patched), accurate estimates place `Seq Scan on public.t_name_2` as inner table
and the index-only scan as outer table.

Intuitively the patched plan is reasonable, placing the cheaper index scan on the NLJ outer side.
Still, real `EXPLAIN ANALYZE` shows negligible performance difference, which I cannot currently explain.

My test design may be inadequate. Feedback and discussion about any gaps in my reasoning are welcome.

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