public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alena Rybakina <[email protected]>
To: Ranier Vilela <[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 22:33:23 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAEudQApBzH9JNkDpXARrjFG0WGM2PcU02f6Ga9sLVS2H6z9YAQ@mail.gmail.com>
References: <[email protected]>
<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]>
<CAEudQApBzH9JNkDpXARrjFG0WGM2PcU02f6Ga9sLVS2H6z9YAQ@mail.gmail.com>
On 28.11.2024 22:28, Ranier Vilela wrote:
> 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;
> +
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
Attachments:
[text/x-patch] bugfix.diff (2.5K, ../[email protected]/3-bugfix.diff)
download | inline diff:
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index d827fc9f4d9..5ea0b27d014 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
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 1b0a5f0e9e1..ef8bbf4748c 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1233,6 +1233,23 @@ SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
14
(1 row)
+-- Transform OR-clauses to SAOP's shouldn't be chosen
+SET random_page_cost = 1;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM tenk1 WHERE stringu1 = 'TVAAAA' OR stringu1 = 'TVAAAB';
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: ((stringu1 = 'TVAAAA'::name) OR (stringu1 = 'TVAAAB'::name))
+ -> BitmapOr
+ -> Bitmap Index Scan on hash_tuplesort_idx
+ Index Cond: (stringu1 = 'TVAAAA'::name)
+ -> Bitmap Index Scan on hash_tuplesort_idx
+ Index Cond: (stringu1 = 'TVAAAB'::name)
+(8 rows)
+
+RESET random_page_cost;
DROP INDEX hash_tuplesort_idx;
RESET maintenance_work_mem;
--
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index ddd0d9ad396..0e6529f3f3d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -372,6 +372,11 @@ CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fi
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
+-- Transform OR-clauses to SAOP's shouldn't be chosen
+SET random_page_cost = 1;
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) FROM tenk1 WHERE stringu1 = 'TVAAAA' OR stringu1 = 'TVAAAB';
+RESET random_page_cost;
DROP INDEX hash_tuplesort_idx;
RESET maintenance_work_mem;
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]
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