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