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
