Re: a wrong index choose when statistics is out of date

On 8/3/2024 18:53, Andy Fan wrote:
> I just reviewed the bad queries plan for the past half years internally,
> I found many queries used the Nested loop which is the direct cause. now
> I think I find out a new reason for this, because the missed optimizer
> statistics cause the rows in outer relation to be 1, which make the Nest
> loop is choosed. I'm not sure your idea could help on this or can help
> on this than mine at this aspect.

Having had the same problem for a long time, I've made an attempt and 
invented a patch that probes an index to determine whether the estimated 
constant is within statistics' scope.
I remember David's remark on the overhead problem, but I don't argue it 
here. This patch is on the table to have one more solution sketch for 
further discussion.
Also, Andy, if you have a specific problem with index choosing, you can 
try a tiny option that makes the index-picking technique less dependent 
on the ordering of index lists [1].

[1] 
https://www.postgresql.org/message-id/9b3dbf6d-776a-4953-a5a4-60992939321d@postgrespro.ru

-- 
regards,
Andrei Lepikhov
Postgres Professional