public inbox for [email protected]
help / color / mirror / Atom feedFrom: Yura Sokolov <[email protected]>
To: Alexander Korotkov <[email protected]>
Cc: Alena Rybakina <[email protected]>
Cc: Ranier Vilela <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: Andrei Lepikhov <[email protected]>
Cc: jian he <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Nikolay Shaplov <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: Peter Geoghegan <[email protected]>
Cc: Marcos Pegoraro <[email protected]>
Cc: [email protected]
Cc: Peter Eisentraut <[email protected]>
Subject: Re: POC, WIP: OR-clause support for indexes
Date: Mon, 13 Jan 2025 03:47:56 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPpHfdv+jtNwofg-p5z86jLYZUTt6tR17Wy00ta0dL=wHQN3ZA@mail.gmail.com>
References: <[email protected]>
<CAPpHfdu9QJ=Gbua3CUUH2KKG_8urakJTen4JD47PGh9wWP=QxQ@mail.gmail.com>
<CAPpHfds3m=55cY1ea1TRzUAgD3pgwmvqm=exVMdOM4q+YT6kHg@mail.gmail.com>
<[email protected]>
<CAPpHfdvxF1OZUoJr2bg8cmAnty-KyRkswPa3hCPSmR5gSx7-Yg@mail.gmail.com>
<CACJufxGS_MKqkfnw3BMhfi+=xuf2SAFvwf0Eq3e12XqAQaKdZg@mail.gmail.com>
<[email protected]>
<CAPpHfduzBgV3AecMU0jFqOSjK9iP86HiHEzj2Hv6hLqWu7JJFQ@mail.gmail.com>
<CAPpHfduqOuvh6i=jYDPWnAUg325hsOLUAW9r_awdirQRA7uzHA@mail.gmail.com>
<[email protected]>
<CAPpHfdtPkA3XGM+FOanXfFNm45YEMkuqpfL0ZSL7uOP1bHPPEg@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAEudQApBzH9JNkDpXARrjFG0WGM2PcU02f6Ga9sLVS2H6z9YAQ@mail.gmail.com>
<[email protected]>
<CAPpHfdvM4E-wPCRbYkr9eGH2sSxgOYKZ1iOUyjKYqTEyurFRLQ@mail.gmail.com>
<43db6a40-5c5f-4909-ad62-3060b297e686@ postgrespro.ru>
<CAPpHfdtgcye9ELaLHw4ZOUxWQ9t1P=oBDJX8KwwOHqasESdXQQ@mail.gmail.com>
<CAPpHfdv+jtNwofg-p5z86jLYZUTt6tR17Wy00ta0dL=wHQN3ZA@mail.gmail.com>
<div>Вс, 12 янв. 2025 г. в 21:39, Alexander Korotkov <[email protected]>:<br></div><div><div class="gmail_quote gmail_quote_container"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">On Fri, Nov 29, 2024 at 9:54 AM Alexander Korotkov <[email protected]> wrote:
<br>> On Fri, Nov 29, 2024 at 7:51 AM Alena Rybakina
<br>> <[email protected]> wrote:
<br>> >
<br>> > On 29.11.2024 03:04, Alexander Korotkov wrote:
<br>> > > On Thu, Nov 28, 2024 at 9:33 PM Alena Rybakina
<br>> > > <[email protected]> wrote:
<br>> > >> On 28.11.2024 22:28, Ranier Vilela wrote:
<br>> > >>
<br>> > >> Em qui., 28 de nov. de 2024 às 16:03, Alena Rybakina <[email protected]> escreveu:
<br>> > >>> Hi! Thank you for the case.
<br>> > >>>
<br>> > >>> On 28.11.2024 21:00, Alexander Lakhin wrote:
<br>> > >>>> Hello Alexander,
<br>> > >>>>
<br>> > >>>> 21.11.2024 09:34, Alexander Korotkov wrote:
<br>> > >>>>> I'm going to push this if no objections.
<br>> > >>>> Please look at the following query, which triggers an error after
<br>> > >>>> ae4569161:
<br>> > >>>> SET random_page_cost = 1;
<br>> > >>>> CREATE TABLE tbl(u UUID);
<br>> > >>>> CREATE INDEX idx ON tbl USING HASH (u);
<br>> > >>>> SELECT COUNT(*) FROM tbl WHERE u = '00000000000000000000000000000000' OR
<br>> > >>>> u = '11111111111111111111111111111111';
<br>> > >>>>
<br>> > >>>> ERROR: XX000: ScalarArrayOpExpr index qual found where not allowed
<br>> > >>>> LOCATION: ExecIndexBuildScanKeys, nodeIndexscan.c:1625
<br>> > >>>>
<br>> > >>>>
<br>> > >>> I found out what the problem is index scan method was not generated. We
<br>> > >>> need to check this during OR clauses for SAOP transformation.
<br>> > >>>
<br>> > >>> There is a patch to fix this problem.
<br>> > >> Hi.
<br>> > >> Thanks for the quick fix.
<br>> > >>
<br>> > >> But I wonder if it is not possible to avoid all if the index is useless?
<br>> > >> Maybe moving your fix to the beginning of the function?
<br>> > >>
<br>> > >> diff --git a/src/backend/optimizer/path/indxpath.<wbr>c b/src/backend/optimizer/path/indxpath.c
<br>> > >> index d827fc9f4d..5ea0b27d01 100644
<br>> > >> --- a/src/backend/optimizer/path/indxpath.c
<br>> > >> +++ b/src/backend/optimizer/path/indxpath.c
<br>> > >> @@ -3248,6 +3248,10 @@ match_orclause_to_indexcol(PlannerInfo *root,
<br>> > >> Assert(IsA(orclause, BoolExpr));
<br>> > >> Assert(orclause->boolop == OR_EXPR);
<br>> > >>
<br>> > >> + /* Ignore index if it doesn't support index scans */
<br>> > >> + if(!index->amsearcharray)
<br>> > >> + return NULL;
<br>> > >> +
<br>> > >>
<br>> > >> Agree. I have updated the patch
<br>> > >>
<br>> > >> /*
<br>> > >> * Try to convert a list of OR-clauses to a single SAOP expression. Each
<br>> > >> * OR entry must be in the form: (indexkey operator constant) or (constant
<br>> > >>
<br>> > >> The test bug:
<br>> > >> EXPLAIN SELECT COUNT(*) FROM tbl WHERE u = '00000000000000000000000000000000'<wbr> OR u = '11111111111111111111111111111111';
<br>> > >> QUERY PLAN
<br>> > >> --------------------------------<wbr>--------------------------------<wbr>--------------------------------<wbr>----------------------------------
<br>> > >> Aggregate (cost=12.46..12.47 rows=1 width=8)
<br>> > >> -> Bitmap Heap Scan on tbl (cost=2.14..12.41 rows=18 width=0)
<br>> > >> Recheck Cond: ((u = '00000000-0000-0000-0000-000000000000'<wbr>::uuid) OR (u = '11111111-1111-1111-1111-111111111111'<wbr>::uuid))
<br>> > >> -> BitmapOr (cost=2.14..2.14 rows=18 width=0)
<br>> > >> -> Bitmap Index Scan on idx (cost=0.00..1.07 rows=9 width=0)
<br>> > >> Index Cond: (u = '00000000-0000-0000-0000-000000000000'<wbr>::uuid)
<br>> > >> -> Bitmap Index Scan on idx (cost=0.00..1.07 rows=9 width=0)
<br>> > >> Index Cond: (u = '11111111-1111-1111-1111-111111111111'<wbr>::uuid)
<br>> > >> (8 rows)
<br>> > > I slightly revised the fix and added similar check to
<br>> > > group_similar_or_args(). Could you, please, review that before
<br>> > > commit?
<br>> > >
<br>> > I agree with changes. Thank you!
<br>>
<br>> Andrei, Alena, thank you for the feedback. Pushed!
<br>
<br>I think we should give some more attention to the patch enabling OR to
<br>SAOP transformation for joins (first time posted in [1]). I think we
<br>tried to only work with Const and Param, because we were previously
<br>working during parse stage. So, at that stage if we have the clause
<br>like "a.x = 1 OR a.x = b.x OR b.x = 2", then we don't know if we
<br>should transform it into "a.x = ANY(1, b.x) OR b.x = 2" or into "a.x
<br>=1 OR b.x = ANY(a.x, 2)". But if we do the transformation during the
<br>index matching, we would actually be able to try the both and select
<br>the best.
</blockquote><div dir="auto"><br></div><div dir="auto">But why not “a.x = ANY(1, b.x) OR b.x = ANY(a.x, 2)” ? Looks strange, but correct ))</div><div dir="auto"><br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;" dir="auto"><br>The revised patch is attached. Most notably it revises
<br>group_similar_or_args() to have the same notion of const-ness as
<br>others. In that function we split potential index key and constant
<br>early to save time on enumerating all possible index keys. But it
<br>appears to be possible to split by relids bitmapsets: index key should
<br>use our relid, while const shouldn't. Other that that, comments,
<br>commit message and naming are revised.
<br>
<br>Links.
<br>1. https://www.postgresql.org/message-<wbr>id/CAPpHfdu9QJ%3DGbua3CUUH2KKG_8urakJTen4JD47PGh9wWP%3...
<br>
<br>------
<br>Regards,
<br>Alexander Korotkov
<br>Supabase
<br></blockquote></div></div>
view thread (38+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: POC, WIP: OR-clause support for indexes
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox