Re: POC, WIP: OR-clause support for indexes

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