public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ranier Vilela <[email protected]>
To: Alena Rybakina <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: Alexander Korotkov <[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: Thu, 28 Nov 2024 16:28:04 -0300
Message-ID: <CAEudQApBzH9JNkDpXARrjFG0WGM2PcU02f6Ga9sLVS2H6z9YAQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAPpHfds4wGdWQ2xx1OUKZC4ggqS6Q=cBSck9Re1ajY0ESkwcmw@mail.gmail.com>
<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>
<[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]>
Em qui., 28 de nov. de 2024 às 16:03, Alena Rybakina <
[email protected]> 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;
+
/*
* 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)
best regards,
Ranier Vilela
view thread (78+ messages)
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]
Subject: Re: POC, WIP: OR-clause support for indexes
In-Reply-To: <CAEudQApBzH9JNkDpXARrjFG0WGM2PcU02f6Ga9sLVS2H6z9YAQ@mail.gmail.com>
* 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