Message-ID: <e3338e82-a28d-4631-9eec-b9c0984b32d5@postgrespro.ru>
Date: Tue, 13 Feb 2024 17:03:20 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: POC, WIP: OR-clause support for indexes
Content-Language: en-US
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>
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <CACJufxFSfSGZpmVt_98SRojdugW2s9H-qYm2sYr0A3o-xPea0g@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/e3338e82-a28d-4631-9eec-b9c0984b32d5%40postgrespro.ru>
Precedence: bulk

On 13/2/2024 07:00, jian he wrote:
> + newa = makeNode(ArrayExpr);
> + /* array_collid will be set by parse_collate.c */
> + newa->element_typeid = scalar_type;
> + newa->array_typeid = array_type;
> + newa->multidims = false;
> + newa->elements = aexprs;
> + newa->location = -1;
> 
> I am confused by the comments `array_collid will be set by
> parse_collate.c`, can you further explain it?
I wonder if the second paragraph of comments on commit b310b6e will be 
enough to dive into details.

> if OR expression right arm is not plain Const, but with collation
> specification, eg.
> `where a  = 'a' collate "C" or a = 'b' collate "C";`
> 
> then the rightop is not Const, it will be CollateExpr, it will not be
> used in transformation.
Yes, it is done for simplicity right now. I'm not sure about corner 
cases of merging such expressions.

> 
> set enable_or_transformation to on;
> explain(timing off, analyze, costs off)
> select count(*) from test where (x = 1 or x = 2 or x = 3 or x = 4 or x
> = 5 or x = 6 or x = 7 or x = 8 or x = 9 ) \watch i=0.1 c=10
> 35.376 ms
> 
> The time is the last result of the 10 iterations.
The reason here - parallel workers.
If you see into the plan you will find parallel workers without 
optimization and absence of them in the case of optimization:

Gather  (cost=1000.00..28685.37 rows=87037 width=12)
	(actual rows=90363 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    ->  Parallel Seq Scan on test
          Filter: ((x = 1) OR (x = 2) OR (x = 3) OR (x = 4) OR (x = 5) 
OR (x = 6) OR (x = 7) OR (x = 8) OR (x = 9))

Seq Scan on test  (cost=0.02..20440.02 rows=90600 width=12)
		  (actual rows=90363 loops=1)
    Filter: (x = ANY ('{1,2,3,4,5,6,7,8,9}'::integer[]))

Having 90600 tuples returned we estimate it into 87000 (less precisely) 
without transformation and 90363 (more precisely) with the transformation.
But if you play with parallel_tuple_cost and parallel_setup_cost, you 
will end up having these parallel workers:

  Gather  (cost=0.12..11691.03 rows=90600 width=12)
	 (actual rows=90363 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    ->  Parallel Seq Scan on test
          Filter: (x = ANY ('{1,2,3,4,5,6,7,8,9}'::integer[]))
          Rows Removed by Filter: 303212

And some profit about 25%, on my laptop.
I'm not sure about the origins of such behavior, but it seems to be an 
issue of parallel workers, not this specific optimization.

-- 
regards,
Andrei Lepikhov
Postgres Professional