Re: Optimization outcome depends on the index order - Mailing list pgsql-hackers

From Andrei Lepikhov
Subject Re: Optimization outcome depends on the index order
Date
Msg-id 08ca9992-80e9-445f-8e82-59d215978a39@postgrespro.ru
Whole thread Raw
In response to Re: Optimization outcome depends on the index order  (Alexander Korotkov <aekorotkov@gmail.com>)
List pgsql-hackers
On 25/12/2023 18:36, Alexander Korotkov wrote:
> On Fri, Dec 22, 2023 at 7:24 PM Andrei Lepikhov
> <a.lepikhov@postgrespro.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




pgsql-hackers by date:

Previous
From: "Anton A. Melnikov"
Date:
Subject: Re: Some performance degradation in REL_16 vs REL_15
Next
From: Masahiko Sawada
Date:
Subject: Re: [PoC] Improve dead tuple storage for lazy vacuum