Re: Incremental Sort Cost Estimation Instability

On 11/7/24 18:06, Alena Rybakina wrote:
> On 07.11.2024 08:57, Andrei Lepikhov wrote:
>> That happens because when estimating the number of groups, Postgres
>> doesn't consider EquivalenceClass, which can let him correct group
>> estimation at a low price.
>> It may be done inside the make_pathkeys_for_sortclauses_extended by
>> choosing a column with a lower number of distinct, but IMO, it is
>> better to do it at the moment of the number of groups estimation.
>>
>> Thoughts? Is it a real issue or just a non-practical corner case?
>>
>> The new version of the patch is attached.
>>
>> [1] https://www.postgresql.org/message-id/
>> flat/8742aaa8-9519-4a1f-91bd-364aec65f5cf%40gmail.com
>
> But you haven’t considered the case when you need to use non-cached
> values, for example, if ndistinct has already changed. Look, here x has
> a minimum ndistinct, and then column z:

> but the order of the columns does not change, as you can see.
I'm unsure what you mean by talking about 'cached value' or 'changed
ndistinct' even slightly.
Also, I don't understand the issue you tried to show with your examples.
My point was that an equality expression can be used to modify
statistics-based decisions on the number of groups. Look:

A.x, distincts = 1000
A.y, distincts = 10

After the filter 'A.x=A.y' it is impossible to get more than 10 groups
on the A.x as well as on the A.y column. So, we have a tool to correct
the estimation considering equivalence classes.

--
regards, Andrei Lepikhov