Feedback-ID: i0fe9450f:Fastmail
Date: Mon, 30 Jan 2023 19:39:30 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: Postgres hackers <pgsql-hackers@lists.postgresql.org>,
	"Drouvot, Bertrand" <bdrouvot@amazon.com>
Subject: Re: Generating code for query jumbling through gen_node_support.pl
Message-ID: <Y9eeYinDb1AcpWrG@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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="BKbBZesdNrR/+239"
Content-Disposition: inline
In-Reply-To: <Y9M+C2obzgRnXPNd@paquier.xyz>
Archived-At: 
 <https://www.postgresql.org/message-id/Y9eeYinDb1AcpWrG%40paquier.xyz>
Precedence: bulk


--BKbBZesdNrR/+239
Content-Type: multipart/mixed; boundary="lKZ79HeW445Ixrn/"
Content-Disposition: inline


--lKZ79HeW445Ixrn/
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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=3Don):
>           query          |  mode  | iterations | avg_runtime_ns | avg_jum=
ble_ns=20
> -------------------------+--------+------------+----------------+--------=
-------
>  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_n=
s=20
----------------------+--------+------------+----------------+-------------=
--
 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

--lKZ79HeW445Ixrn/
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="jumble-benchmark.txt"
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/query=
jumblefuncs.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 =3D NULL;
=20
 	Assert(IsQueryIdEnabled());
=20
+	elog(WARNING, "start JumbleQuery");
+
 	if (query->utilityStmt)
 	{
 		query->queryId =3D compute_utility_query_id(querytext,
@@ -114,18 +118,26 @@ JumbleQuery(Query *query, const char *querytext)
 	else
 	{
 		jstate =3D (JumbleState *) palloc(sizeof(JumbleState));
-
-		/* Set up workspace for query jumbling */
 		jstate->jumble =3D (unsigned char *) palloc(JUMBLE_SIZE);
-		jstate->jumble_len =3D 0;
 		jstate->clocations_buf_size =3D 32;
 		jstate->clocations =3D (LocationLen *)
 			palloc(jstate->clocations_buf_size * sizeof(LocationLen));
-		jstate->clocations_count =3D 0;
-		jstate->highest_extern_param_id =3D 0;
=20
-		/* Compute query ID and mark the Query node with it */
-		JumbleQueryInternal(jstate, query);
+		for (int i =3D 0; i < MAX_QUERY_COMPUTE ; i++)
+		{
+			/* Set up workspace for query jumbling */
+			memset(jstate->jumble, 0, sizeof(JUMBLE_SIZE));
+			jstate->jumble_len =3D 0;
+			jstate->clocations_buf_size =3D 32;
+			memset(jstate->clocations, 0,
+				   jstate->clocations_buf_size * sizeof(LocationLen));
+
+			jstate->clocations_count =3D 0;
+			jstate->highest_extern_param_id =3D 0;
+
+			/* Compute query ID and mark the Query node with it */
+			JumbleQueryInternal(jstate, query);
+		}
 		query->queryId =3D DatumGetUInt64(hash_any_extended(jstate->jumble,
 														  jstate->jumble_len,
 														  0));
@@ -138,6 +150,8 @@ JumbleQuery(Query *query, const char *querytext)
 			query->queryId =3D UINT64CONST(1);
 	}
=20
+	elog(WARNING, "end JumbleQuery");
+
 	return jstate;
 }
=20

--lKZ79HeW445Ixrn/--

--BKbBZesdNrR/+239
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmPXnmIACgkQnvQgOdby
QH2GJw//YfJu43ciJanRIyaQkSSC2PWNbAXYc9HGNWeFcjbG/hqDbnODhXyQHZws
PyWVwy/uv2xQE+fS8Q2q12AMj1ZcatOEGd8RzLyAGGYPzBdhQdj8AMrpAoBhGJIZ
dQYa1ESxFbLPUoLaUVfq6Nq5QQZ1ktrYSAbKdbPRQyQrkcd4K4AZ0OhIUrOzwmAz
rt45mdta4aPfJjXeym8A1NtEFrLI54pQRSPW2Zg7aNz5RaLY8tAu+dTbBLxZuYzJ
wdw37I8OF1A/cssCrMvrXpxk6TOldCtu1THCRWLRd90Y0ntHoFV2VoETU/OVe+Is
vIogyG8lW+Nq5ZQTJmFNRmAYebOMjin+AXsswJR2GN8v3578x/N5PrT4ETHkKUBu
mxaU6IhWXADpRVcl30Mo+zPT7RBjn+Q+SjHjinKGBOujgNfa091i0kpTEfGhoACz
bnLHDeTX7off72Gn6cBl01BLrgOjHUdZMcDqdues69VsfhzhDZmpXGbuUN2fRC0X
rVMKRgOVgmdbwTkHnYiwksWuteFdxlKNo2sI7OWgMqQxV2ZeojG4OvGPruO5J6D8
V/EShntUXjBp0Ixie5LJ55Ga5QvxHycs0M1/0zTXJpHho6cinNhfYcs+ZEkAZe0B
LXoJvDvXC8pwet3mL9IH2rRzOVj3cBUa/9ZHAhbe0e4IooSya/U=
=NIDv
-----END PGP SIGNATURE-----

--BKbBZesdNrR/+239--