Re: Optimization outcome depends on the index order

On 25/12/2023 18:36, Alexander Korotkov wrote:
> On Fri, Dec 22, 2023 at 7:24 PM Andrei Lepikhov
> <a(dot)lepikhov(at)postgrespro(dot)ru> wrote:
>> On 22/12/2023 11:48, Alexander Korotkov wrote:
>>> > Because we must trust all predictions made by the planner, we just
>>> > choose the most trustworthy path. According to the planner logic, it is
>>> > a path with a smaller selectivity. We can make mistakes anyway just
>>> > because of the nature of estimation.
>>>
>>> Even if we need to take selectivity into account here, it's still not
>>> clear why this should be on top of other logic later in add_path().
>> I got your point now, thanks for pointing it out. In the next version of
>> the patch selectivity is used as a criteria only in the case of COSTS_EQUAL.
>
> It looks better now. But it's hard for me to judge these heuristics
> in add_path(). Tom, what do you think about this?
Just food for thought:
Another approach I have considered was to initialize the relation index
list according to some consistent rule: place unique indexes at the head
of the list, arrange indexes according to the number of columns involved
and sort in some lexical order.
But IMO, the implemented approach looks better.

--
regards,
Andrei Lepikhov
Postgres Professional