public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Geier <[email protected]>
To: [email protected]
Subject: Hashed IN only applied to first encountered IN
Date: Tue, 1 Apr 2025 13:51:18 +0200
Message-ID: <[email protected]> (raw)
Hi hackers,
The hashed IN optimization is only applied to the first encountered
ScalarArrayOpExpr during the expression tree traversal in
convert_saop_to_hashed_saop_walker(). Reason being that the walker
returns true which aborts the traversal.
This can be exhibited by running a query with two IN statements vs the
same query with just a single IN statement. The IN statements are
combined via OR and both statements return no rows to prevent any kind
of lazy evaluation optimizations. The query with two IN statements is 6x
slower than the the query with the single IN statement. See the attached
example.
I've also attached a patch with a fix.
--
David Geier
(ServiceNow)
CREATE TABLE test (col TEXT);
INSERT INTO test select 'testtesttest' FROM generate_series(1, 10000000);
\timing on
SELECT * FROM
test
WHERE
col IN ('testtesttesttesa', 'testtesttesttesb', 'testtesttesttesc', 'testtesttesttesd', 'testtesttesttese', 'testtesttesttesf', 'testtesttesttesg', 'testtesttesttesh', 'testtesttesttesi', 'testtesttesttesj', 'testtesttesttesk', 'testtesttesttesl', 'testtesttesttesm', 'testtesttesttesn', 'testtesttestteso', 'testtesttesttesp', 'testtesttesttesq', 'testtesttesttesr', 'testtesttesttess', 'testtesttesttes-', 'testtesttesttesu', 'testtesttesttesv', 'testtesttesttesw', 'testtesttesttesx', 'testtesttesttesy', 'testtesttesttesz', 'testtesttesttes1', 'testtesttesttes2', 'testtesttesttes3', 'testtesttesttes4', 'testtesttesttes5', 'testtesttesttes6', 'testtesttesttes7', 'testtesttesttes8', 'testtesttesttes9', 'testtesttesttes0');
SELECT * FROM
test
WHERE
col IN ('testtesttesttesa', 'testtesttesttesb', 'testtesttesttesc', 'testtesttesttesd', 'testtesttesttese', 'testtesttesttesf', 'testtesttesttesg', 'testtesttesttesh', 'testtesttesttesi', 'testtesttesttesj', 'testtesttesttesk', 'testtesttesttesl', 'testtesttesttesm', 'testtesttesttesn', 'testtesttestteso', 'testtesttesttesp', 'testtesttesttesq', 'testtesttesttesr', 'testtesttesttess', 'testtesttesttes-', 'testtesttesttesu', 'testtesttesttesv', 'testtesttesttesw', 'testtesttesttesx', 'testtesttesttesy', 'testtesttesttesz', 'testtesttesttes1', 'testtesttesttes2', 'testtesttesttes3', 'testtesttesttes4', 'testtesttesttes5', 'testtesttesttes6', 'testtesttesttes7', 'testtesttesttes8', 'testtesttesttes9', 'testtesttesttes0')
OR
col IN ('testtesttesttesa2', 'testtesttesttesb', 'testtesttesttesc', 'testtesttesttesd', 'testtesttesttese', 'testtesttesttesf', 'testtesttesttesg', 'testtesttesttesh', 'testtesttesttesi', 'testtesttesttesj', 'testtesttesttesk', 'testtesttesttesl', 'testtesttesttesm', 'testtesttesttesn', 'testtesttestteso', 'testtesttesttesp', 'testtesttesttesq', 'testtesttesttesr', 'testtesttesttess', 'testtesttesttes-', 'testtesttesttesu', 'testtesttesttesv', 'testtesttesttesw', 'testtesttesttesx', 'testtesttesttesy', 'testtesttesttesz', 'testtesttesttes1', 'testtesttesttes2', 'testtesttesttes3', 'testtesttesttes4', 'testtesttesttes5', 'testtesttesttes6', 'testtesttesttes7', 'testtesttesttes8', 'testtesttesttes9', 'testtesttesttes0');
From 7de592928acada597f6e4314036abd87a6af6403 Mon Sep 17 00:00:00 2001
From: David Geier <[email protected]>
Date: Tue, 1 Apr 2025 13:47:27 +0200
Subject: [PATCH] Fixed hashed SAOP
---
src/backend/optimizer/util/clauses.c | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 43dfecfb47f..05d33461163 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2326,7 +2326,7 @@ convert_saop_to_hashed_saop_walker(Node *node, void *context)
/* Looks good. Fill in the hash functions */
saop->hashfuncid = lefthashfunc;
}
- return true;
+ return false;
}
}
else /* !saop->useOr */
@@ -2364,7 +2364,7 @@ convert_saop_to_hashed_saop_walker(Node *node, void *context)
*/
saop->negfuncid = get_opcode(negator);
}
- return true;
+ return false;
}
}
}
--
2.34.1
Attachments:
[text/plain] example.sql (2.3K, ../[email protected]/2-example.sql)
download | inline:
CREATE TABLE test (col TEXT);
INSERT INTO test select 'testtesttest' FROM generate_series(1, 10000000);
\timing on
SELECT * FROM
test
WHERE
col IN ('testtesttesttesa', 'testtesttesttesb', 'testtesttesttesc', 'testtesttesttesd', 'testtesttesttese', 'testtesttesttesf', 'testtesttesttesg', 'testtesttesttesh', 'testtesttesttesi', 'testtesttesttesj', 'testtesttesttesk', 'testtesttesttesl', 'testtesttesttesm', 'testtesttesttesn', 'testtesttestteso', 'testtesttesttesp', 'testtesttesttesq', 'testtesttesttesr', 'testtesttesttess', 'testtesttesttes-', 'testtesttesttesu', 'testtesttesttesv', 'testtesttesttesw', 'testtesttesttesx', 'testtesttesttesy', 'testtesttesttesz', 'testtesttesttes1', 'testtesttesttes2', 'testtesttesttes3', 'testtesttesttes4', 'testtesttesttes5', 'testtesttesttes6', 'testtesttesttes7', 'testtesttesttes8', 'testtesttesttes9', 'testtesttesttes0');
SELECT * FROM
test
WHERE
col IN ('testtesttesttesa', 'testtesttesttesb', 'testtesttesttesc', 'testtesttesttesd', 'testtesttesttese', 'testtesttesttesf', 'testtesttesttesg', 'testtesttesttesh', 'testtesttesttesi', 'testtesttesttesj', 'testtesttesttesk', 'testtesttesttesl', 'testtesttesttesm', 'testtesttesttesn', 'testtesttestteso', 'testtesttesttesp', 'testtesttesttesq', 'testtesttesttesr', 'testtesttesttess', 'testtesttesttes-', 'testtesttesttesu', 'testtesttesttesv', 'testtesttesttesw', 'testtesttesttesx', 'testtesttesttesy', 'testtesttesttesz', 'testtesttesttes1', 'testtesttesttes2', 'testtesttesttes3', 'testtesttesttes4', 'testtesttesttes5', 'testtesttesttes6', 'testtesttesttes7', 'testtesttesttes8', 'testtesttesttes9', 'testtesttesttes0')
OR
col IN ('testtesttesttesa2', 'testtesttesttesb', 'testtesttesttesc', 'testtesttesttesd', 'testtesttesttese', 'testtesttesttesf', 'testtesttesttesg', 'testtesttesttesh', 'testtesttesttesi', 'testtesttesttesj', 'testtesttesttesk', 'testtesttesttesl', 'testtesttesttesm', 'testtesttesttesn', 'testtesttestteso', 'testtesttesttesp', 'testtesttesttesq', 'testtesttesttesr', 'testtesttesttess', 'testtesttesttes-', 'testtesttesttesu', 'testtesttesttesv', 'testtesttesttesw', 'testtesttesttesx', 'testtesttesttesy', 'testtesttesttesz', 'testtesttesttes1', 'testtesttesttes2', 'testtesttesttes3', 'testtesttesttes4', 'testtesttesttes5', 'testtesttesttes6', 'testtesttesttes7', 'testtesttesttes8', 'testtesttesttes9', 'testtesttesttes0');
[text/plain] 0001-Fixed-hashed-SAOP.patch (983B, ../[email protected]/3-0001-Fixed-hashed-SAOP.patch)
download | inline diff:
From 7de592928acada597f6e4314036abd87a6af6403 Mon Sep 17 00:00:00 2001
From: David Geier <[email protected]>
Date: Tue, 1 Apr 2025 13:47:27 +0200
Subject: [PATCH] Fixed hashed SAOP
---
src/backend/optimizer/util/clauses.c | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 43dfecfb47f..05d33461163 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2326,7 +2326,7 @@ convert_saop_to_hashed_saop_walker(Node *node, void *context)
/* Looks good. Fill in the hash functions */
saop->hashfuncid = lefthashfunc;
}
- return true;
+ return false;
}
}
else /* !saop->useOr */
@@ -2364,7 +2364,7 @@ convert_saop_to_hashed_saop_walker(Node *node, void *context)
*/
saop->negfuncid = get_opcode(negator);
}
- return true;
+ return false;
}
}
}
--
2.34.1
view thread (3+ 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]
Subject: Re: Hashed IN only applied to first encountered IN
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