Re: Postgres picks suboptimal index after building of an extended statistics

On 18/12/2023 15:29, Alexander Korotkov wrote:
> Also, there is a set of patches [7], [8], and [9], which makes the 
> optimizer consider path selectivity as long as path costs during the 
> path selection.  I've rechecked that none of these patches could resolve 
> the original problem described in [1].
It is true. We accidentally mixed two different problems in one thread.
>  Also, I think they are quite 
> tricky.  The model of our optimizer assumes that paths in the list 
> should be the different ways of getting the same result.  If we choose 
> the paths by their selectivity, that breaks this model.  I don't say 
> there is no way for this.  But if we do this, that would require 
> significant rethinking of our optimizer model and possible revision of a 
> significant part of it.
I can't understand that. In [9] we just elaborate the COSTS_EQUAL case 
and establish final decision on more stable basis than a casual order of 
indexes in the list.
>  Anyway, I think if there is still interest in 
> this, that should be moved into a separate thread to keep this thread 
> focused on the problem described in [1].
Agree. IMO, the problem of optimizer dependency on an order of indexes 
in the relation index list is more urgent for now.
> 
> Finally, I'd like to note that the issue described in [1] is mostly the 
> selectivity estimation problem.  It could be solved by adding the 
> multi-column MCV statistics.  The patches published so far look more 
> like hacks for particular use cases rather than appropriate solutions.  
> It still looks promising to me to use the knowledge of unique 
> constraints during selectivity estimation [10].  Even though it's hard 
> to implement and possibly implies some overhead, it fits the current 
> model.  I also think unique contracts could probably be used in some way 
> to improve estimates even when there is no full match.
I have tried to use the knowledge about unique indexes in the 
selectivity estimation routine. But it looks invasive and adds a lot of 
overhead.

-- 
regards,
Andrei Lepikhov
Postgres Professional