public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andrei Lepikhov <[email protected]>
To: jian he <[email protected]>
Cc: Alena Rybakina <[email protected]>
Cc: Alexander Korotkov <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: [email protected]
Cc: Peter Geoghegan <[email protected]>
Cc: Finnerty, Jim <[email protected]>
Cc: Marcos Pegoraro <[email protected]>
Cc: [email protected]
Cc: Ranier Vilela <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Subject: Re: POC, WIP: OR-clause support for indexes
Date: Wed, 14 Feb 2024 10:21:41 +0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<CA+TgmoZCgP6FrBQEusn4yaWm02XU8OPeoEMk91q7PRBgwaAkFw@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CACJufxGXhJ823cdAdp2Ho7qC-HZ3_-dtdj-myaAi_u9RQLn45g@mail.gmail.com>
	<CACJufxG=wB6qLbOAWaYEQHccN+qi=6-pw7zKVWZ53ZiDZNRTRA@mail.gmail.com>
	<CACJufxFrZS07oBHMk1_c8P3A84VZ3ysXiZV8NeU6gAnvu+HsVA@mail.gmail.com>
	<[email protected]>
	<CACJufxFS-xcjaWq2Du2OyJUjRAyqCk12Q_zGOPxv61sgrdpw9w@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CACJufxFSfSGZpmVt_98SRojdugW2s9H-qYm2sYr0A3o-xPea0g@mail.gmail.com>
	<[email protected]>

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

view thread (24+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: POC, WIP: OR-clause support for indexes
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox