MIME-Version: 1.0
References: <567ED6CA.2040504@sigaev.ru> <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> <CAPpHfdvM4E-wPCRbYkr9eGH2sSxgOYKZ1iOUyjKYqTEyurFRLQ@mail.gmail.com>
 <CAPpHfdtgcye9ELaLHw4ZOUxWQ9t1P=oBDJX8KwwOHqasESdXQQ@mail.gmail.com>
 <CAPpHfdv+jtNwofg-p5z86jLYZUTt6tR17Wy00ta0dL=wHQN3ZA@mail.gmail.com> <Mime4j.0.10773454d7da3b8f.1945d21b049@imap.gmail.com>
In-Reply-To: <Mime4j.0.10773454d7da3b8f.1945d21b049@imap.gmail.com>
From: Alexander Korotkov <aekorotkov@gmail.com>
Date: Mon, 13 Jan 2025 03:06:42 +0200
Message-ID: <CAPpHfdtXZocMW_+BYjcLUenzfZmzz5z88NXHpR5HbBtoqAtDuw@mail.gmail.com>
Subject: Re: POC, WIP: OR-clause support for indexes
To: Yura Sokolov <y.sokolov@postgrespro.ru>
Cc: Alena Rybakina <a.rybakina@postgrespro.ru>, 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: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAPpHfdtXZocMW_%2BBYjcLUenzfZmzz5z88NXHpR5HbBtoqAtDuw%40mail.gmail.com>
Precedence: bulk

On Mon, Jan 13, 2025 at 2:47=E2=80=AFAM Yura Sokolov <y.sokolov@postgrespro=
.ru> wrote:
> =D0=92=D1=81, 12 =D1=8F=D0=BD=D0=B2. 2025=E2=80=AF=D0=B3. =D0=B2 21:39, A=
lexander Korotkov <aekorotkov@gmail.com>:
>>
>> On Fri, Nov 29, 2024 at 9:54=E2=80=AFAM Alexander Korotkov <aekorotkov@g=
mail.com> wrote:
>> > On Fri, Nov 29, 2024 at 7:51=E2=80=AFAM Alena Rybakina
>> > <a.rybakina@postgrespro.ru> wrote:
>> > >
>> > > On 29.11.2024 03:04, Alexander Korotkov wrote:
>> > > > 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.ryba=
kina@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 aft=
er
>> > > >>>> 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 '0000000000000000000000000=
0000000' OR
>> > > >>>> u =3D '11111111111111111111111111111111';
>> > > >>>>
>> > > >>>> ERROR: XX000: ScalarArrayOpExpr index qual found where not allo=
wed
>> > > >>>> LOCATION: ExecIndexBuildScanKeys, nodeIndexscan.c:1625
>> > > >>>>
>> > > >>>>
>> > > >>> I found out what the problem is index scan method was not genera=
ted. 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 u=
seless?
>> > > >> 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 *ro=
ot,
>> > > >> 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 (=
constant
>> > > >>
>> > > >> The test bug:
>> > > >> EXPLAIN SELECT COUNT(*) FROM tbl WHERE u =3D '0000000000000000000=
0000000000000' 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=3D=
0)
>> > > >> Recheck Cond: ((u =3D '00000000-0000-0000-0000-000000000000'::uui=
d) 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-000000000000'::uuid)
>> > > >> -> Bitmap Index Scan on idx (cost=3D0.00..1.07 rows=3D9 width=3D0=
)
>> > > >> Index Cond: (u =3D '11111111-1111-1111-1111-111111111111'::uuid)
>> > > >> (8 rows)
>> > > > I slightly revised the fix and added similar check to
>> > > > group_similar_or_args(). Could you, please, review that before
>> > > > commit?
>> > > >
>> > > I agree with changes. Thank you!
>> >
>> > Andrei, Alena, thank you for the feedback. Pushed!
>>
>> I think we should give some more attention to the patch enabling OR to
>> SAOP transformation for joins (first time posted in [1]). I think we
>> tried to only work with Const and Param, because we were previously
>> working during parse stage. So, at that stage if we have the clause
>> like "a.x =3D 1 OR a.x =3D b.x OR b.x =3D 2", then we don't know if we
>> should transform it into "a.x =3D ANY(1, b.x) OR b.x =3D 2" or into "a.x
>> =3D1 OR b.x =3D ANY(a.x, 2)". But if we do the transformation during the
>> index matching, we would actually be able to try the both and select
>> the best.
>
>
> But why not =E2=80=9Ca.x =3D ANY(1, b.x) OR b.x =3D ANY(a.x, 2)=E2=80=9D =
? Looks strange, but correct ))

That could probably work for a parse stage, but as you can check that
approach has a lot of other problems.  As we do during index matching,
that doesn't matter.  I just wanted to state that nothing in the
current approach prevent us from working the same way for joins.

------
Regards,
Alexander Korotkov
Supabase