MIME-Version: 1.0
References: <567ED6CA.2040504@sigaev.ru> <CA+TgmoZ3tQ3Zwzo+9uXQL_tf0rDHQHKeZ4bGt1LDvL+_dqghUg@mail.gmail.com>
 <CAPpHfdt8kowRDUkmOnO7_WJJQ1uk+O379JiZCk_9_Pt5AQ4+0w@mail.gmail.com>
 <CAPpHfdu9QJ=Gbua3CUUH2KKG_8urakJTen4JD47PGh9wWP=QxQ@mail.gmail.com>
 <CAPpHfds3m=55cY1ea1TRzUAgD3pgwmvqm=exVMdOM4q+YT6kHg@mail.gmail.com>
 <45050ba9-3777-4cc1-89ad-a02deebe7729@gmail.com> <CAPpHfdvxF1OZUoJr2bg8cmAnty-KyRkswPa3hCPSmR5gSx7-Yg@mail.gmail.com>
 <CACJufxGS_MKqkfnw3BMhfi+=xuf2SAFvwf0Eq3e12XqAQaKdZg@mail.gmail.com>
 <0026f562-d403-4f97-b9dc-3fe3a279e8c9@postgrespro.ru> <CAPpHfduzBgV3AecMU0jFqOSjK9iP86HiHEzj2Hv6hLqWu7JJFQ@mail.gmail.com>
 <CAPpHfduqOuvh6i=jYDPWnAUg325hsOLUAW9r_awdirQRA7uzHA@mail.gmail.com>
 <37bf870a-22e0-4aba-b845-549482118f8d@gmail.com> <CAPpHfdtPkA3XGM+FOanXfFNm45YEMkuqpfL0ZSL7uOP1bHPPEg@mail.gmail.com>
 <8174de69-9e1a-0827-0e81-ef97f56a5939@gmail.com> <ed2b909c-35d4-4a39-ac38-8a15634a281b@postgrespro.ru>
 <CAEudQApBzH9JNkDpXARrjFG0WGM2PcU02f6Ga9sLVS2H6z9YAQ@mail.gmail.com> <3b717020-14d9-425f-8c60-ce336b5a23b3@postgrespro.ru>
In-Reply-To: <3b717020-14d9-425f-8c60-ce336b5a23b3@postgrespro.ru>
From: Alexander Korotkov <aekorotkov@gmail.com>
Date: Fri, 29 Nov 2024 02:04:05 +0200
Message-ID: <CAPpHfdvM4E-wPCRbYkr9eGH2sSxgOYKZ1iOUyjKYqTEyurFRLQ@mail.gmail.com>
Subject: Re: POC, WIP: OR-clause support for indexes
To: Alena Rybakina <a.rybakina@postgrespro.ru>
Cc: Ranier Vilela <ranier.vf@gmail.com>, Alexander Lakhin <exclusion@gmail.com>, 
	Andrei Lepikhov <lepihov@gmail.com>, jian he <jian.universality@gmail.com>, 
	Robert Haas <robertmhaas@gmail.com>, Nikolay Shaplov <dhyan@nataraj.su>, 
	pgsql-hackers@lists.postgresql.org, pgsql-hackers@postgresql.org, 
	Peter Geoghegan <pg@bowt.ie>, Marcos Pegoraro <marcos@f10.com.br>, teodor@sigaev.ru, 
	Peter Eisentraut <peter@eisentraut.org>
Content-Type: multipart/mixed; boundary="0000000000009967ae062801f092"
Archived-At: <https://www.postgresql.org/message-id/CAPpHfdvM4E-wPCRbYkr9eGH2sSxgOYKZ1iOUyjKYqTEyurFRLQ%40mail.gmail.com>
Precedence: bulk

--0000000000009967ae062801f092
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Nov 28, 2024 at 9:33=E2=80=AFPM Alena Rybakina
<a.rybakina@postgrespro.ru> wrote:
>
> On 28.11.2024 22:28, Ranier Vilela wrote:
>
> Em qui., 28 de nov. de 2024 =C3=A0s 16:03, Alena Rybakina <a.rybakina@pos=
tgrespro.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 =3D 1;
>> > CREATE TABLE tbl(u UUID);
>> > CREATE INDEX idx ON tbl USING HASH (u);
>> > SELECT COUNT(*) FROM tbl WHERE u =3D '00000000000000000000000000000000=
' OR
>> >   u =3D '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/optimize=
r/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 =3D=3D 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 (consta=
nt
>
> The test bug:
> EXPLAIN SELECT COUNT(*) FROM tbl WHERE u =3D '000000000000000000000000000=
00000' OR u =3D '11111111111111111111111111111111';
>                                                             QUERY PLAN
> -------------------------------------------------------------------------=
---------------------------------------------------------
>  Aggregate  (cost=3D12.46..12.47 rows=3D1 width=3D8)
>    ->  Bitmap Heap Scan on tbl  (cost=3D2.14..12.41 rows=3D18 width=3D0)
>          Recheck Cond: ((u =3D '00000000-0000-0000-0000-000000000000'::uu=
id) OR (u =3D '11111111-1111-1111-1111-111111111111'::uuid))
>          ->  BitmapOr  (cost=3D2.14..2.14 rows=3D18 width=3D0)
>                ->  Bitmap Index Scan on idx  (cost=3D0.00..1.07 rows=3D9 =
width=3D0)
>                      Index Cond: (u =3D '00000000-0000-0000-0000-00000000=
0000'::uuid)
>                ->  Bitmap Index Scan on idx  (cost=3D0.00..1.07 rows=3D9 =
width=3D0)
>                      Index Cond: (u =3D '11111111-1111-1111-1111-11111111=
1111'::uuid)
> (8 rows)

I slightly revised the fix and added similar check to
group_similar_or_args().  Could you, please, review that before
commit?

------
Regards,
Alexander Korotkov
Supabase

--0000000000009967ae062801f092
Content-Type: application/octet-stream; 
	name="v3-0001-Skip-not-SOAP-supported-indexes-while-transformin.patch"
Content-Disposition: attachment; 
	filename="v3-0001-Skip-not-SOAP-supported-indexes-while-transformin.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_m41zcrbv0>
X-Attachment-Id: f_m41zcrbv0

RnJvbSBiOWZjN2Q1MmViYjA1MWZlNWY2NzUwMTQwN2M1NmRhZjBhZjc2NWRhIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBBbGV4YW5kZXIgS29yb3Rrb3YgPGFrb3JvdGtvdkBwb3N0Z3Jl
c3FsLm9yZz4KRGF0ZTogRnJpLCAyOSBOb3YgMjAyNCAwMTo0Njo0MyArMDIwMApTdWJqZWN0OiBb
UEFUQ0ggdjNdIFNraXAgbm90IFNPQVAtc3VwcG9ydGVkIGluZGV4ZXMgd2hpbGUgdHJhbnNmb3Jt
aW5nIGFuIE9SCiBjbGF1c2UgaW50byBTQU9QCgpUaGVyZSBpcyBubyBwb2ludCBpbiB0cmFuc2Zv
cm1pbmcgT1ItY2xhdXNlcyBpbnRvIFNBT1AncyBpZiB0aGUgdGFyZ2V0IGluZGV4CmRvZXNuJ3Qg
c3VwcG9ydCBTQU9QIHNjYW5zIGFueXdheS4gIFRoaXMgY29tbWl0IGFkZHMgY29ycmVzcG9uZGlu
ZyBjaGVja3MKdG8gbWF0Y2hfb3JjbGF1c2VfdG9faW5kZXhjb2woKSBhbmQgZ3JvdXBfc2ltaWxh
cl9vcl9hcmdzKCkuICBUaGUgZmlyc3QgY2hlY2sKZml4ZXMgdGhlIGFjdHVhbCBidWcsIHdoaWxl
IHRoZSBzZWNvbmQganVzdCBzYXZlcyBzb21lIGN5Y2xlcy4KClJlcG9ydGVkLWJ5OiBBbGV4YW5k
ZXIgTGFraGluCkRpc2N1c3Npb246IGh0dHBzOi8vcG9zdGdyLmVzL20vODE3NGRlNjktOWUxYS0w
ODI3LTBlODEtZWY5N2Y1NmE1OTM5JTQwZ21haWwuY29tCkF1dGhvcjogQWxlbmEgUnliYWtpbmEK
UmV2aWV3ZWQtYnk6IFJhbmllciBWaWxlbGEsIEFsZXhhbmRlciBLb3JvdGtvdgotLS0KIHNyYy9i
YWNrZW5kL29wdGltaXplci9wYXRoL2luZHhwYXRoLmMgICAgICB8IDExICsrKysrKysrKy0tCiBz
cmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2NyZWF0ZV9pbmRleC5vdXQgfCAxOCArKysrKysrKysr
KysrKysrKysKIHNyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2NyZWF0ZV9pbmRleC5zcWwgICAgICB8ICA2
ICsrKysrKwogMyBmaWxlcyBjaGFuZ2VkLCAzMyBpbnNlcnRpb25zKCspLCAyIGRlbGV0aW9ucygt
KQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2luZHhwYXRoLmMgYi9z
cmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9pbmR4cGF0aC5jCmluZGV4IGQ4MjdmYzlmNGQ5Li42
NDBiMjhhNjhmYyAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvaW5keHBh
dGguYworKysgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9pbmR4cGF0aC5jCkBAIC0xMzU0
LDggKzEzNTQsMTEgQEAgZ3JvdXBfc2ltaWxhcl9vcl9hcmdzKFBsYW5uZXJJbmZvICpyb290LCBS
ZWxPcHRJbmZvICpyZWwsIFJlc3RyaWN0SW5mbyAqcmluZm8pCiAJCXsKIAkJCUluZGV4T3B0SW5m
byAqaW5kZXggPSAoSW5kZXhPcHRJbmZvICopIGxmaXJzdChsYzIpOwogCi0JCQkvKiBJZ25vcmUg
aW5kZXggaWYgaXQgZG9lc24ndCBzdXBwb3J0IGJpdG1hcCBzY2FucyAqLwotCQkJaWYgKCFpbmRl
eC0+YW1oYXNnZXRiaXRtYXApCisJCQkvKgorCQkJICogSWdub3JlIGluZGV4IGlmIGl0IGRvZXNu
J3Qgc3VwcG9ydCBiaXRtYXAgc2NhbnMgb3IgU0FPUAorCQkJICogY2xhdXNlcy4KKwkJCSAqLwor
CQkJaWYgKCFpbmRleC0+YW1oYXNnZXRiaXRtYXAgfHwgIWluZGV4LT5hbXNlYXJjaGFycmF5KQog
CQkJCWNvbnRpbnVlOwogCiAJCQlmb3IgKGNvbG51bSA9IDA7IGNvbG51bSA8IGluZGV4LT5ua2V5
Y29sdW1uczsgY29sbnVtKyspCkBAIC0zMjQ4LDYgKzMyNTEsMTAgQEAgbWF0Y2hfb3JjbGF1c2Vf
dG9faW5kZXhjb2woUGxhbm5lckluZm8gKnJvb3QsCiAJQXNzZXJ0KElzQShvcmNsYXVzZSwgQm9v
bEV4cHIpKTsKIAlBc3NlcnQob3JjbGF1c2UtPmJvb2xvcCA9PSBPUl9FWFBSKTsKIAorCS8qIEln
bm9yZSBpbmRleCBpZiBpdCBkb2Vzbid0IHN1cHBvcnQgU0FPUCBjbGF1c2VzICovCisJaWYoIWlu
ZGV4LT5hbXNlYXJjaGFycmF5KQorCQlyZXR1cm4gTlVMTDsKKwogCS8qCiAJICogVHJ5IHRvIGNv
bnZlcnQgYSBsaXN0IG9mIE9SLWNsYXVzZXMgdG8gYSBzaW5nbGUgU0FPUCBleHByZXNzaW9uLiBF
YWNoCiAJICogT1IgZW50cnkgbXVzdCBiZSBpbiB0aGUgZm9ybTogKGluZGV4a2V5IG9wZXJhdG9y
IGNvbnN0YW50KSBvciAoY29uc3RhbnQKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhw
ZWN0ZWQvY3JlYXRlX2luZGV4Lm91dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvY3JlYXRl
X2luZGV4Lm91dAppbmRleCAxYjBhNWYwZTllMS4uMTkwNGViNjViYjkgMTAwNjQ0Ci0tLSBhL3Ny
Yy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvY3JlYXRlX2luZGV4Lm91dAorKysgYi9zcmMvdGVzdC9y
ZWdyZXNzL2V4cGVjdGVkL2NyZWF0ZV9pbmRleC5vdXQKQEAgLTEyMzMsNiArMTIzMywyNCBAQCBT
RUxFQ1QgY291bnQoKikgRlJPTSB0ZW5rMSBXSEVSRSBzdHJpbmd1MSA9ICdUVkFBQUEnOwogICAg
IDE0CiAoMSByb3cpCiAKKy0tIE9SLWNsYXVzZXMgc2hvdWxkbid0IGJlIHRyYW5zZm9ybWVkIGlu
dG8gU0FPUCBiZWNhdXNlIGhhc2ggaW5kZXhlcyBkb24ndAorLS0gc3VwcG9ydCBTQU9QIHNjYW5z
LgorU0VUIGVuYWJsZV9zZXFzY2FuID0gb2ZmOworRVhQTEFJTiAoQ09TVFMgT0ZGKQorU0VMRUNU
IENPVU5UKCopIEZST00gdGVuazEgV0hFUkUgc3RyaW5ndTEgPSAnVFZBQUFBJyBPUiAgc3RyaW5n
dTEgPSAnVFZBQUFCJzsKKyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVS
WSBQTEFOICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIAorLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tCisgQWdncmVnYXRlCisgICAtPiAgQml0bWFwIEhlYXAgU2NhbiBvbiB0
ZW5rMQorICAgICAgICAgUmVjaGVjayBDb25kOiAoKHN0cmluZ3UxID0gJ1RWQUFBQSc6Om5hbWUp
IE9SIChzdHJpbmd1MSA9ICdUVkFBQUInOjpuYW1lKSkKKyAgICAgICAgIC0+ICBCaXRtYXBPcgor
ICAgICAgICAgICAgICAgLT4gIEJpdG1hcCBJbmRleCBTY2FuIG9uIGhhc2hfdHVwbGVzb3J0X2lk
eAorICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29uZDogKHN0cmluZ3UxID0gJ1RWQUFBQSc6
Om5hbWUpCisgICAgICAgICAgICAgICAtPiAgQml0bWFwIEluZGV4IFNjYW4gb24gaGFzaF90dXBs
ZXNvcnRfaWR4CisgICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAoc3RyaW5ndTEgPSAn
VFZBQUFCJzo6bmFtZSkKKyg4IHJvd3MpCisKK1JFU0VUIGVuYWJsZV9zZXFzY2FuOwogRFJPUCBJ
TkRFWCBoYXNoX3R1cGxlc29ydF9pZHg7CiBSRVNFVCBtYWludGVuYW5jZV93b3JrX21lbTsKIC0t
CmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9jcmVhdGVfaW5kZXguc3FsIGIvc3Jj
L3Rlc3QvcmVncmVzcy9zcWwvY3JlYXRlX2luZGV4LnNxbAppbmRleCBkZGQwZDlhZDM5Ni4uYzA4
NWUwNWYwNTIgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2NyZWF0ZV9pbmRleC5z
cWwKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwvY3JlYXRlX2luZGV4LnNxbApAQCAtMzcyLDYg
KzM3MiwxMiBAQCBDUkVBVEUgSU5ERVggaGFzaF90dXBsZXNvcnRfaWR4IE9OIHRlbmsxIFVTSU5H
IGhhc2ggKHN0cmluZ3UxIG5hbWVfb3BzKSBXSVRIIChmaQogRVhQTEFJTiAoQ09TVFMgT0ZGKQog
U0VMRUNUIGNvdW50KCopIEZST00gdGVuazEgV0hFUkUgc3RyaW5ndTEgPSAnVFZBQUFBJzsKIFNF
TEVDVCBjb3VudCgqKSBGUk9NIHRlbmsxIFdIRVJFIHN0cmluZ3UxID0gJ1RWQUFBQSc7CistLSBP
Ui1jbGF1c2VzIHNob3VsZG4ndCBiZSB0cmFuc2Zvcm1lZCBpbnRvIFNBT1AgYmVjYXVzZSBoYXNo
IGluZGV4ZXMgZG9uJ3QKKy0tIHN1cHBvcnQgU0FPUCBzY2Fucy4KK1NFVCBlbmFibGVfc2Vxc2Nh
biA9IG9mZjsKK0VYUExBSU4gKENPU1RTIE9GRikKK1NFTEVDVCBDT1VOVCgqKSBGUk9NIHRlbmsx
IFdIRVJFIHN0cmluZ3UxID0gJ1RWQUFBQScgT1IgIHN0cmluZ3UxID0gJ1RWQUFBQic7CitSRVNF
VCBlbmFibGVfc2Vxc2NhbjsKIERST1AgSU5ERVggaGFzaF90dXBsZXNvcnRfaWR4OwogUkVTRVQg
bWFpbnRlbmFuY2Vfd29ya19tZW07CiAKLS0gCjIuMzkuNSAoQXBwbGUgR2l0LTE1NCkKCg==
--0000000000009967ae062801f092--