Message-ID: <1cf6b382-6e27-4268-9f2a-54ada66eb2eb@postgrespro.ru>
Date: Wed, 14 Feb 2024 10:21:41 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: POC, WIP: OR-clause support for indexes
Content-Language: en-US
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: jian he <jian.universality@gmail.com>
Cc: Alena Rybakina <a.rybakina@postgrespro.ru>,
 Alexander Korotkov <aekorotkov@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>, pgsql-hackers@postgresql.org,
 Peter Geoghegan <pg@bowt.ie>, "Finnerty, Jim" <jfinnert@amazon.com>,
 Marcos Pegoraro <marcos@f10.com.br>, teodor@sigaev.ru,
 Ranier Vilela <ranier.vf@gmail.com>,
 Tomas Vondra <tomas.vondra@enterprisedb.com>,
 Peter Eisentraut <peter@eisentraut.org>
References: <567ED6CA.2040504@sigaev.ru>
 <04ef16eb-46c1-4ce7-9f68-d1c80ef0be81@postgrespro.ru>
 <CA+TgmoZCgP6FrBQEusn4yaWm02XU8OPeoEMk91q7PRBgwaAkFw@mail.gmail.com>
 <cfdc7fef-cf0b-437b-89fa-62ffd6677f7d@postgrespro.ru>
 <59e67a40-95a8-4d74-ae4e-027ea0f59084@postgrespro.ru>
 <59b77a8a-29d6-4727-bf26-4a30a6a9719a@postgrespro.ru>
 <32d23d97-d07f-4d44-9933-ba3295fb3ed8@postgrespro.ru>
 <aec17a1a-9de0-4994-8a53-c6b4e64afb17@postgrespro.ru>
 <CACJufxGXhJ823cdAdp2Ho7qC-HZ3_-dtdj-myaAi_u9RQLn45g@mail.gmail.com>
 <CACJufxG=wB6qLbOAWaYEQHccN+qi=6-pw7zKVWZ53ZiDZNRTRA@mail.gmail.com>
 <CACJufxFrZS07oBHMk1_c8P3A84VZ3ysXiZV8NeU6gAnvu+HsVA@mail.gmail.com>
 <a155849d-7e74-4700-8c55-8a945d42935e@postgrespro.ru>
 <CACJufxFS-xcjaWq2Du2OyJUjRAyqCk12Q_zGOPxv61sgrdpw9w@mail.gmail.com>
 <7e11e27b-7ab9-4d59-af0a-a921861a9206@postgrespro.ru>
 <13ad923a-33df-4b44-9e00-c9323cd4c37d@postgrespro.ru>
 <CACJufxFSfSGZpmVt_98SRojdugW2s9H-qYm2sYr0A3o-xPea0g@mail.gmail.com>
 <e3338e82-a28d-4631-9eec-b9c0984b32d5@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <e3338e82-a28d-4631-9eec-b9c0984b32d5@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/1cf6b382-6e27-4268-9f2a-54ada66eb2eb%40postgrespro.ru>
Precedence: bulk

On 13/2/2024 17:03, Andrei Lepikhov wrote:
> On 13/2/2024 07:00, jian he wrote:
>> The time is the last result of the 10 iterations.
> I'm not sure about the origins of such behavior, but it seems to be an 
> issue of parallel workers, not this specific optimization.
Having written that, I'd got a backburner. And to close that issue, I 
explored get_restriction_qual_cost(). A close look shows us that "x IN 
(..)" cheaper than its equivalent "x=N1 OR ...". Just numbers:

ANY: startup_cost = 0.0225; total_cost = 0.005
OR: startup_cost==0; total_cost = 0.0225

Expression total_cost is calculated per tuple. In your example, we have 
many tuples, so the low cost of expression per tuple dominates over the 
significant startup cost.

According to the above, SAOP adds 6250 to the cost of SeqScan; OR - 
13541. So, the total cost of the query with SAOP is less than with OR, 
and the optimizer doesn't choose heavy parallel workers. And it is the 
answer.

So, this example is more about the subtle balance between 
parallel/sequential execution, which can vary from one platform to another.

-- 
regards,
Andrei Lepikhov
Postgres Professional