pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Michael Paquier <michael@paquier.xyz>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Cc: Drouvot, Bertrand <bdrouvot@amazon.com>
Subject: Re: Generating code for query jumbling through gen_node_support.pl
Date: Mon, 30 Jan 2023 19:39:30 +0900
Message-ID: <Y9eeYinDb1AcpWrG@paquier.xyz> (raw )
In-Reply-To: <Y9M+C2obzgRnXPNd@paquier.xyz >
References: <Y8ZTvD6nEqNttAHJ@paquier.xyz >
<Y8eZ9/3STyhF57RV@paquier.xyz >
<50b571dc-e0c0-22be-9803-4a3480d2e792@enterprisedb.com >
<Y8oaA5Vxnr4rzEDJ@paquier.xyz >
<e4d61d52-4992-d190-b5ad-48c6c752942c@enterprisedb.com >
<Y8tdmPlzFyNcrPR3@paquier.xyz >
<bf604c8d-b6e9-284b-327a-cac8417a74e8@enterprisedb.com >
<Y8+BdCOjxykre5es@paquier.xyz >
<9695f035-0f2b-92b9-deed-0b7e5bb7e4e0@enterprisedb.com >
<Y9M+C2obzgRnXPNd@paquier.xyz >
On Fri, Jan 27, 2023 at 11:59:47AM +0900, Michael Paquier wrote:
> Using that, I can compile the following results for various cases (-O2
> and compute_query_id=on):
> query | mode | iterations | avg_runtime_ns | avg_jumble_ns
> -------------------------+--------+------------+----------------+---------------
> begin | string | 50000000 | 4.53116 | 4.54
> begin | jumble | 50000000 | 30.94578 | 30.94
> commit | string | 50000000 | 4.76004 | 4.74
> commit | jumble | 50000000 | 31.4791 | 31.48
> create table 1 column | string | 50000000 | 7.22836 | 7.08
> create table 1 column | jumble | 50000000 | 152.10852 | 151.96
> create table 5 columns | string | 50000000 | 12.43412 | 12.28
> create table 5 columns | jumble | 50000000 | 352.88976 | 349.1
> create table 20 columns | string | 5000000 | 49.591 | 48.2
> create table 20 columns | jumble | 5000000 | 2272.4066 | 2271
> drop table 1 column | string | 50000000 | 6.70538 | 6.56
> drop table 1 column | jumble | 50000000 | 50.38 | 50.24
> drop table 5 columns | string | 50000000 | 6.88256 | 6.74
> drop table 5 columns | jumble | 50000000 | 50.02898 | 49.9
> SET work_mem | string | 50000000 | 7.28752 | 7.28
> SET work_mem | jumble | 50000000 | 91.66588 | 91.64
> (16 rows)
Just to close the loop here, I have done more measurements to compare
the jumble done for some DMLs and some SELECTs between HEAD and the
patch (forgot to post some last Friday). Both methods show comparable
results:
query | mode | iterations | avg_runtime_ns | avg_jumble_ns
----------------------+--------+------------+----------------+---------------
insert table 10 cols | master | 50000000 | 377.17878 | 377.04
insert table 10 cols | jumble | 50000000 | 409.47924 | 409.34
insert table 20 cols | master | 50000000 | 692.94924 | 692.8
insert table 20 cols | jumble | 50000000 | 710.0901 | 709.96
insert table 5 cols | master | 50000000 | 232.44308 | 232.3
insert table 5 cols | jumble | 50000000 | 253.49854 | 253.36
select 10 cols | master | 50000000 | 449.13608 | 383.36
select 10 cols | jumble | 50000000 | 491.61912 | 323.86
select 5 cols | master | 50000000 | 277.477 | 277.46
select 5 cols | jumble | 50000000 | 323.88152 | 323.86
(10 rows)
The averages are in ns, so the difference does not bother me much.
There may be some noise mixed in that ;)
(Attached is the tweak I have applied on HEAD to get some numbers.)
--
Michael
Attachments:
jumble-benchmark.txt (text/plain, 1.9K) download
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index 16084842a3..c66090bde3 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -101,10 +101,14 @@ CleanQuerytext(const char *query, int *location, int *len)
JumbleState *
JumbleQuery(Query *query, const char *querytext)
{
+#define MAX_QUERY_COMPUTE 50000000
+
JumbleState *jstate = NULL;
Assert(IsQueryIdEnabled());
+ elog(WARNING, "start JumbleQuery");
+
if (query->utilityStmt)
{
query->queryId = compute_utility_query_id(querytext,
@@ -114,18 +118,26 @@ JumbleQuery(Query *query, const char *querytext)
else
{
jstate = (JumbleState *) palloc(sizeof(JumbleState));
-
- /* Set up workspace for query jumbling */
jstate->jumble = (unsigned char *) palloc(JUMBLE_SIZE);
- jstate->jumble_len = 0;
jstate->clocations_buf_size = 32;
jstate->clocations = (LocationLen *)
palloc(jstate->clocations_buf_size * sizeof(LocationLen));
- jstate->clocations_count = 0;
- jstate->highest_extern_param_id = 0;
- /* Compute query ID and mark the Query node with it */
- JumbleQueryInternal(jstate, query);
+ for (int i = 0; i < MAX_QUERY_COMPUTE ; i++)
+ {
+ /* Set up workspace for query jumbling */
+ memset(jstate->jumble, 0, sizeof(JUMBLE_SIZE));
+ jstate->jumble_len = 0;
+ jstate->clocations_buf_size = 32;
+ memset(jstate->clocations, 0,
+ jstate->clocations_buf_size * sizeof(LocationLen));
+
+ jstate->clocations_count = 0;
+ jstate->highest_extern_param_id = 0;
+
+ /* Compute query ID and mark the Query node with it */
+ JumbleQueryInternal(jstate, query);
+ }
query->queryId = DatumGetUInt64(hash_any_extended(jstate->jumble,
jstate->jumble_len,
0));
@@ -138,6 +150,8 @@ JumbleQuery(Query *query, const char *querytext)
query->queryId = UINT64CONST(1);
}
+ elog(WARNING, "end JumbleQuery");
+
return jstate;
}
download
view thread (44+ messages) latest in thread
Message-ID: <Y9eeYinDb1AcpWrG@paquier.xyz>
Permalink: ../Y9eeYinDb1AcpWrG@paquier.xyz/
Also on: postgresql.org/message-id/Y9eeYinDb1AcpWrG@paquier.xyz
copy link · copy postgr.es
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: pgsql-hackers@postgresql.org
Cc: michael@paquier.xyz, peter.eisentraut@enterprisedb.com, pgsql-hackers@lists.postgresql.org, bdrouvot@amazon.com
Subject: Re: Generating code for query jumbling through gen_node_support.pl
In-Reply-To: <Y9eeYinDb1AcpWrG@paquier.xyz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox