Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tGkGZ-000Si4-Fx for pgsql-hackers@arkaria.postgresql.org; Thu, 28 Nov 2024 19:33:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tGkGW-0010pL-Hz for pgsql-hackers@arkaria.postgresql.org; Thu, 28 Nov 2024 19:33:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tGkGW-0010p5-1v for pgsql-hackers@lists.postgresql.org; Thu, 28 Nov 2024 19:33:29 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tGkGT-0001Pt-Kz for pgsql-hackers@lists.postgresql.org; Thu, 28 Nov 2024 19:33:28 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1732822405; bh=zAb2VLPCbmTwlAoYmzSjF7SKbhwdXmmM8iE+f6pBrfc=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=zjF7xVil6LjmVTcLbnkOYjqO+QBJCKGzxT7IrsuC0AZ2xtCQNHPDLNxIMqfWiaRDu FgJn0KzJhZz3ktXqv2rs/8wAupR2fc7kEAsxWgCM0QPSFjLQBw9f6TV3n5Wj3ItosZ dd74Xc1K8sX6GlNwCU039hLIOz7LA7eTGpneVKQCVGFlfiAII4o0DmUUFSt0IHgdFg 1VfFByi0xhudTaP7hbxoLZzy4vL2T38SiibGDd/nVYsM9Mpw7wpYuGogaRurXtgDj4 Eh9P4tMrEtVEcOaSwlyEVdecrrNJQPz6zUJyuH8suIG1G+QGjEid+6DKyXYzVRvV0F +luZ59D5Rqhwg== Received: from [172.20.10.4] (unknown [89.113.147.44]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (Client did not present a certificate) (Authenticated sender: a.rybakina@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 770986077C; Thu, 28 Nov 2024 22:33:24 +0300 (MSK) Content-Type: multipart/mixed; boundary="------------ED3Pc1a3dcPJJSfKjtlDnIhF" Message-ID: <3b717020-14d9-425f-8c60-ce336b5a23b3@postgrespro.ru> Date: Thu, 28 Nov 2024 22:33:23 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: POC, WIP: OR-clause support for indexes To: Ranier Vilela Cc: Alexander Lakhin , Alexander Korotkov , Andrei Lepikhov , jian he , Robert Haas , Nikolay Shaplov , pgsql-hackers@lists.postgresql.org, pgsql-hackers@postgresql.org, Peter Geoghegan , Marcos Pegoraro , teodor@sigaev.ru, Peter Eisentraut References: <567ED6CA.2040504@sigaev.ru> <45050ba9-3777-4cc1-89ad-a02deebe7729@gmail.com> <0026f562-d403-4f97-b9dc-3fe3a279e8c9@postgrespro.ru> <37bf870a-22e0-4aba-b845-549482118f8d@gmail.com> <8174de69-9e1a-0827-0e81-ef97f56a5939@gmail.com> Content-Language: en-US From: Alena Rybakina In-Reply-To: X-KSMG-AntiPhishing: NotDetected, bases: 2024/11/28 17:36:00 X-KSMG-AntiSpam-Interceptor-Info: not scanned X-KSMG-AntiSpam-Status: not scanned, disabled by settings X-KSMG-AntiVirus: Kaspersky Secure Mail Gateway, version 2.1.0.7854, bases: 2024/11/28 17:42:00 #26906956 X-KSMG-AntiVirus-Status: NotDetected, skipped X-KSMG-LinksScanning: not scanned, disabled by settings X-KSMG-Message-Action: skipped X-KSMG-Rule-ID: 1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ED3Pc1a3dcPJJSfKjtlDnIhF Content-Type: multipart/alternative; boundary="------------Fddn0P5zATd0XgDubBHKC6rl" --------------Fddn0P5zATd0XgDubBHKC6rl Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 28.11.2024 22:28, Ranier Vilela wrote: > Em qui., 28 de nov. de 2024 às 16:03, Alena Rybakina > escreveu: > > Hi! Thank you for the case. > > On 28.11.2024 21:00, Alexander Lakhin wrote: > > Hello Alexander, > > > > 21.11.2024 09:34, Alexander Korotkov wrote: > >> I'm going to push this if no objections. > > > > Please look at the following query, which triggers an error after > > ae4569161: > > SET random_page_cost = 1; > > CREATE TABLE tbl(u UUID); > > CREATE INDEX idx ON tbl USING HASH (u); > > SELECT COUNT(*) FROM tbl WHERE u = > '00000000000000000000000000000000' OR > >   u = '11111111111111111111111111111111'; > > > > ERROR:  XX000: ScalarArrayOpExpr index qual found where not allowed > > LOCATION:  ExecIndexBuildScanKeys, nodeIndexscan.c:1625 > > > > > I found out what the problem is index scan method was not > generated. We > need to check this during OR clauses for SAOP transformation. > > There is a patch to fix this problem. > > Hi. > Thanks for the quick fix. > > But I wonder if it is not possible to avoid all if the index is useless? > Maybe moving your fix to the beginning of the function? > > diff --git a/src/backend/optimizer/path/indxpath.c > b/src/backend/optimizer/path/indxpath.c > index d827fc9f4d..5ea0b27d01 100644 > --- a/src/backend/optimizer/path/indxpath.c > +++ b/src/backend/optimizer/path/indxpath.c > @@ -3248,6 +3248,10 @@ match_orclause_to_indexcol(PlannerInfo *root, >   Assert(IsA(orclause, BoolExpr)); >   Assert(orclause->boolop == OR_EXPR); > > + /* Ignore index if it doesn't support index scans */ > + if(!index->amsearcharray) > + return NULL; > + Agree. I have updated the patch >   /* >   * Try to convert a list of OR-clauses to a single SAOP expression. Each >   * OR entry must be in the form: (indexkey operator constant) or > (constant > > The test bug: > EXPLAIN SELECT COUNT(*) FROM tbl WHERE u = > '00000000000000000000000000000000' OR u = > '11111111111111111111111111111111'; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------------- >  Aggregate  (cost=12.46..12.47 rows=1 width=8) >    ->  Bitmap Heap Scan on tbl  (cost=2.14..12.41 rows=18 width=0) >          Recheck Cond: ((u = > '00000000-0000-0000-0000-000000000000'::uuid) OR (u = > '11111111-1111-1111-1111-111111111111'::uuid)) >          ->  BitmapOr  (cost=2.14..2.14 rows=18 width=0) >                ->  Bitmap Index Scan on idx  (cost=0.00..1.07 rows=9 > width=0) >                      Index Cond: (u = > '00000000-0000-0000-0000-000000000000'::uuid) >                ->  Bitmap Index Scan on idx  (cost=0.00..1.07 rows=9 > width=0) >                      Index Cond: (u = > '11111111-1111-1111-1111-111111111111'::uuid) > (8 rows) > Thank you -- Regards, Alena Rybakina Postgres Professional --------------Fddn0P5zATd0XgDubBHKC6rl Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 28.11.2024 22:28, Ranier Vilela wrote:
Em qui., 28 de nov. de 2024 às 16:03, Alena Rybakina <a.rybakina@postgrespro.ru> escreveu:
Hi! Thank you for the case.

On 28.11.2024 21:00, Alexander Lakhin wrote:
> Hello Alexander,
>
> 21.11.2024 09:34, Alexander Korotkov wrote:
>> I'm going to push this if no objections.
>
> Please look at the following query, which triggers an error after
> ae4569161:
> SET random_page_cost = 1;
> CREATE TABLE tbl(u UUID);
> CREATE INDEX idx ON tbl USING HASH (u);
> SELECT COUNT(*) FROM tbl WHERE u = '00000000000000000000000000000000' OR
>   u = '11111111111111111111111111111111';
>
> ERROR:  XX000: ScalarArrayOpExpr index qual found where not allowed
> LOCATION:  ExecIndexBuildScanKeys, nodeIndexscan.c:1625
>
>
I found out what the problem is index scan method was not generated. We
need to check this during OR clauses for SAOP transformation.

There is a patch to fix this problem.
Hi.
Thanks for the quick fix.

But I wonder if it is not possible to avoid all if the index is useless?
Maybe moving your fix to the beginning of the function?

diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index d827fc9f4d..5ea0b27d01 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -3248,6 +3248,10 @@ match_orclause_to_indexcol(PlannerInfo *root,
  Assert(IsA(orclause, BoolExpr));
  Assert(orclause->boolop == OR_EXPR);
 
+ /* Ignore index if it doesn't support index scans */
+ if(!index->amsearcharray)
+ return NULL;
+
Agree. I have updated the patch
  /*
  * Try to convert a list of OR-clauses to a single SAOP expression. Each
  * OR entry must be in the form: (indexkey operator constant) or (constant

The test bug:
EXPLAIN SELECT COUNT(*) FROM tbl WHERE u = '00000000000000000000000000000000' OR u = '11111111111111111111111111111111';
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=12.46..12.47 rows=1 width=8)
   ->  Bitmap Heap Scan on tbl  (cost=2.14..12.41 rows=18 width=0)
         Recheck Cond: ((u = '00000000-0000-0000-0000-000000000000'::uuid) OR (u = '11111111-1111-1111-1111-111111111111'::uuid))
         ->  BitmapOr  (cost=2.14..2.14 rows=18 width=0)
               ->  Bitmap Index Scan on idx  (cost=0.00..1.07 rows=9 width=0)
                     Index Cond: (u = '00000000-0000-0000-0000-000000000000'::uuid)
               ->  Bitmap Index Scan on idx  (cost=0.00..1.07 rows=9 width=0)
                     Index Cond: (u = '11111111-1111-1111-1111-111111111111'::uuid)
(8 rows) 

Thank you
-- 
Regards,
Alena Rybakina
Postgres Professional
--------------Fddn0P5zATd0XgDubBHKC6rl-- --------------ED3Pc1a3dcPJJSfKjtlDnIhF Content-Type: text/x-patch; charset=UTF-8; name="bugfix.diff" Content-Disposition: attachment; filename="bugfix.diff" Content-Transfer-Encoding: base64 ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2luZHhwYXRoLmMgYi9z cmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9pbmR4cGF0aC5jCmluZGV4IGQ4MjdmYzlmNGQ5 Li41ZWEwYjI3ZDAxNCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgv aW5keHBhdGguYworKysgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9pbmR4cGF0aC5j CkBAIC0zMjQ4LDYgKzMyNDgsMTAgQEAgbWF0Y2hfb3JjbGF1c2VfdG9faW5kZXhjb2woUGxh bm5lckluZm8gKnJvb3QsCiAJQXNzZXJ0KElzQShvcmNsYXVzZSwgQm9vbEV4cHIpKTsKIAlB c3NlcnQob3JjbGF1c2UtPmJvb2xvcCA9PSBPUl9FWFBSKTsKIAorCS8qIElnbm9yZSBpbmRl eCBpZiBpdCBkb2Vzbid0IHN1cHBvcnQgaW5kZXggc2NhbnMgKi8KKwlpZighaW5kZXgtPmFt c2VhcmNoYXJyYXkpCisJCXJldHVybiBOVUxMOworCiAJLyoKIAkgKiBUcnkgdG8gY29udmVy dCBhIGxpc3Qgb2YgT1ItY2xhdXNlcyB0byBhIHNpbmdsZSBTQU9QIGV4cHJlc3Npb24uIEVh Y2gKIAkgKiBPUiBlbnRyeSBtdXN0IGJlIGluIHRoZSBmb3JtOiAoaW5kZXhrZXkgb3BlcmF0 b3IgY29uc3RhbnQpIG9yIChjb25zdGFudApkaWZmIC0tZ2l0IGEvc3JjL3Rlc3QvcmVncmVz cy9leHBlY3RlZC9jcmVhdGVfaW5kZXgub3V0IGIvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3Rl ZC9jcmVhdGVfaW5kZXgub3V0CmluZGV4IDFiMGE1ZjBlOWUxLi5lZjhiYmY0NzQ4YyAxMDA2 NDQKLS0tIGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9jcmVhdGVfaW5kZXgub3V0Cisr KyBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvY3JlYXRlX2luZGV4Lm91dApAQCAtMTIz Myw2ICsxMjMzLDIzIEBAIFNFTEVDVCBjb3VudCgqKSBGUk9NIHRlbmsxIFdIRVJFIHN0cmlu Z3UxID0gJ1RWQUFBQSc7CiAgICAgMTQKICgxIHJvdykKIAorLS0gVHJhbnNmb3JtIE9SLWNs YXVzZXMgdG8gU0FPUCdzIHNob3VsZG4ndCBiZSBjaG9zZW4KK1NFVCByYW5kb21fcGFnZV9j b3N0ID0gMTsKK0VYUExBSU4gKENPU1RTIE9GRikKK1NFTEVDVCBDT1VOVCgqKSBGUk9NIHRl bmsxIFdIRVJFIHN0cmluZ3UxID0gJ1RWQUFBQScgT1IgIHN0cmluZ3UxID0gJ1RWQUFBQic7 CisgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgUVVFUlkgUExBTiAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKKy0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLQorIEFnZ3JlZ2F0ZQorICAgLT4gIEJpdG1hcCBIZWFwIFNjYW4gb24gdGVu azEKKyAgICAgICAgIFJlY2hlY2sgQ29uZDogKChzdHJpbmd1MSA9ICdUVkFBQUEnOjpuYW1l KSBPUiAoc3RyaW5ndTEgPSAnVFZBQUFCJzo6bmFtZSkpCisgICAgICAgICAtPiAgQml0bWFw T3IKKyAgICAgICAgICAgICAgIC0+ICBCaXRtYXAgSW5kZXggU2NhbiBvbiBoYXNoX3R1cGxl c29ydF9pZHgKKyAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6IChzdHJpbmd1MSA9 ICdUVkFBQUEnOjpuYW1lKQorICAgICAgICAgICAgICAgLT4gIEJpdG1hcCBJbmRleCBTY2Fu IG9uIGhhc2hfdHVwbGVzb3J0X2lkeAorICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29u ZDogKHN0cmluZ3UxID0gJ1RWQUFBQic6Om5hbWUpCisoOCByb3dzKQorCitSRVNFVCByYW5k b21fcGFnZV9jb3N0OwogRFJPUCBJTkRFWCBoYXNoX3R1cGxlc29ydF9pZHg7CiBSRVNFVCBt YWludGVuYW5jZV93b3JrX21lbTsKIC0tCmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNz L3NxbC9jcmVhdGVfaW5kZXguc3FsIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwvY3JlYXRlX2lu ZGV4LnNxbAppbmRleCBkZGQwZDlhZDM5Ni4uMGU2NTI5ZjNmM2QgMTAwNjQ0Ci0tLSBhL3Ny Yy90ZXN0L3JlZ3Jlc3Mvc3FsL2NyZWF0ZV9pbmRleC5zcWwKKysrIGIvc3JjL3Rlc3QvcmVn cmVzcy9zcWwvY3JlYXRlX2luZGV4LnNxbApAQCAtMzcyLDYgKzM3MiwxMSBAQCBDUkVBVEUg SU5ERVggaGFzaF90dXBsZXNvcnRfaWR4IE9OIHRlbmsxIFVTSU5HIGhhc2ggKHN0cmluZ3Ux IG5hbWVfb3BzKSBXSVRIIChmaQogRVhQTEFJTiAoQ09TVFMgT0ZGKQogU0VMRUNUIGNvdW50 KCopIEZST00gdGVuazEgV0hFUkUgc3RyaW5ndTEgPSAnVFZBQUFBJzsKIFNFTEVDVCBjb3Vu dCgqKSBGUk9NIHRlbmsxIFdIRVJFIHN0cmluZ3UxID0gJ1RWQUFBQSc7CistLSBUcmFuc2Zv cm0gT1ItY2xhdXNlcyB0byBTQU9QJ3Mgc2hvdWxkbid0IGJlIGNob3NlbgorU0VUIHJhbmRv bV9wYWdlX2Nvc3QgPSAxOworRVhQTEFJTiAoQ09TVFMgT0ZGKQorU0VMRUNUIENPVU5UKCop IEZST00gdGVuazEgV0hFUkUgc3RyaW5ndTEgPSAnVFZBQUFBJyBPUiAgc3RyaW5ndTEgPSAn VFZBQUFCJzsKK1JFU0VUIHJhbmRvbV9wYWdlX2Nvc3Q7CiBEUk9QIElOREVYIGhhc2hfdHVw bGVzb3J0X2lkeDsKIFJFU0VUIG1haW50ZW5hbmNlX3dvcmtfbWVtOwogCg== --------------ED3Pc1a3dcPJJSfKjtlDnIhF--