Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r5Hom-00Clta-OJ for pgsql-hackers@arkaria.postgresql.org; Tue, 21 Nov 2023 03:52:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r5Hol-00CZOx-9f for pgsql-hackers@arkaria.postgresql.org; Tue, 21 Nov 2023 03:52:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r5Hok-00CZOn-UU for pgsql-hackers@lists.postgresql.org; Tue, 21 Nov 2023 03:52:54 +0000 Received: from mail-pl1-x636.google.com ([2607:f8b0:4864:20::636]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r5Hoh-007XTa-Vd for pgsql-hackers@postgresql.org; Tue, 21 Nov 2023 03:52:54 +0000 Received: by mail-pl1-x636.google.com with SMTP id d9443c01a7336-1cc5b705769so45530145ad.0 for ; Mon, 20 Nov 2023 19:52:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1700538769; x=1701143569; darn=postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=OSLJ/P5ZvL7gkiGG4iq4oV1tSbbkT+U2J+HsypP0Vv0=; b=BDpIiA6L54K90rvaXOrrfN6ftzR5QAeFTzU08bS/rgkJJHztgaigNnI0Nh921QE5DY pIbyq01FBNII5LjJ22YSopkQ6RuwndKoMqLE6pc+psrQTRvSkPx/AHJITtnG3X2Xx3B/ Puxlj2D+gRbN9oohGM4DEKWWzk/hxx31srZsFZVp9DXtN/JWH/TZ2GMP15PBDO9WjrXv dDJqSyrxmSqALQmpSISpT3nmk2SPWJF3Ajgi5ieaJtEOnPSY/wbuF6eB7CiU+Px5xIj/ ixtFQbiQnmFm+ryEQC9UPGiGCsgmOF9YW5pI6g+8eWj/9igwAwLgQ4LsN+M38Md/Pgg0 GckQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1700538769; x=1701143569; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=OSLJ/P5ZvL7gkiGG4iq4oV1tSbbkT+U2J+HsypP0Vv0=; b=xCVQyT9PjcvE1E8V3uvMqQwO9PCtxwYjWZVtE0KvX+8kzPvrsnEUm6xBqWKp3HB9pu AjWQSdbrw26kDaqaJCFPxqe/pN0nK4rxTKKxaJFXj3jn7dJPKE4LfBesFJQGgKt4EB/8 MlI9jKF2NjROlNfkAK8S3PIPksCaZFDClVU6dBsGoXi/v0W9ZjpIdTbpwzdT5X9Er6Ty ufzT9B2Nj0NMMJBqYmq9IPHG0C46zRTiK8gidliJ/hLdUN44h40IW/CEDIP1GvC77rqO vsukdC86iiNCDzmP6olXvFYYJkymy4CSMOKUOUr0/vXELgdoCjbloGteJqpej3m8xSke 87JA== X-Gm-Message-State: AOJu0Yxn4bLxNrJnDqtQN7qco5UA+MpU5ilZ03uZesBKymHSIMVoqBPG VfIK08sQe204a5znvT+uZRo= X-Google-Smtp-Source: AGHT+IFkx5oYgxJMDKlyeejpCagrK3CogyvA5u1jy/W+N3SPp2jqtNCH2jTtL2dXvnB+JvVb0/M9tA== X-Received: by 2002:a17:902:db07:b0:1cf:5c99:a615 with SMTP id m7-20020a170902db0700b001cf5c99a615mr7786212plx.35.1700538768894; Mon, 20 Nov 2023 19:52:48 -0800 (PST) Received: from smtpclient.apple (KD106139187093.ppp-bb.dion.ne.jp. [106.139.187.93]) by smtp.gmail.com with ESMTPSA id n6-20020a170902d2c600b001b016313b1dsm6789317plc.86.2023.11.20.19.52.47 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Mon, 20 Nov 2023 19:52:48 -0800 (PST) From: Amit Langote Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_125D3C3B-CDC8-4BE8-BF96-7053596975DC" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.700.6\)) Subject: Re: remaining sql/json patches Date: Tue, 21 Nov 2023 12:52:35 +0900 In-Reply-To: Cc: Alvaro Herrera , Andrew Dunstan , jian he , Erik Rijkers , PostgreSQL-development To: Andres Freund References: <202309281104.kmkr4v34ny2t@alvherre.pgsql> <20231006214856.yie5dedhzrcb3qxx@alap3.anarazel.de> <20231115171119.svvyzevtfvsqkxpx@awork3.anarazel.de> X-Mailer: Apple Mail (2.3731.700.6) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_125D3C3B-CDC8-4BE8-BF96-7053596975DC Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 On Nov 16, 2023, at 17:48, Amit Langote wrote: > On Thu, Nov 16, 2023 at 2:11=E2=80=AFAM Andres Freund = wrote: >> On 2023-11-15 22:00:41 +0900, Amit Langote wrote: >>>> This causes a nontrivial increase in the size of the parser (~5% in = an >>>> optimized build here), I wonder if we can do better. >>>=20 >>> Hmm, sorry if I sound ignorant but what do you mean by the parser = here? >>=20 >> gram.o, in an optimized build. >>=20 >>> I can see that the byte-size of gram.o increases by 1.66% after the >>> above additions (1.72% with previous versions). >>=20 >> I'm not sure anymore how I measured it, but if you just looked at the = total >> file size, that might not show the full gain, because of debug = symbols >> etc. You can use the size command to look at just the code and data = size. >=20 > $ size /tmp/gram.* > text data bss dec hex filename > 661808 0 0 661808 a1930 /tmp/gram.o.unpatched > 672800 0 0 672800 a4420 /tmp/gram.o.patched >=20 > That's still a 1.66% increase in the code size: >=20 > (672800 - 661808) / 661808 % =3D 1.66 >=20 > As for gram.c, the increase is a bit larger: >=20 > $ ll /tmp/gram.* > -rw-rw-r--. 1 amit amit 2605925 Nov 16 16:18 /tmp/gram.c.unpatched > -rw-rw-r--. 1 amit amit 2709422 Nov 16 16:22 /tmp/gram.c.patched >=20 > (2709422 - 2605925) / 2605925 % =3D 3.97 >=20 >>> I've also checked >>> using log_parser_stats that there isn't much slowdown in the >>> raw-parsing speed. >>=20 >> What does "isn't much slowdown" mean in numbers? >=20 > Sure, the benchmark I used measured the elapsed time (using > log_parser_stats) of parsing a simple select statement (*) averaged > over 10000 repetitions of the same query performed with `psql -c`: >=20 > Unpatched: 0.000061 seconds > Patched: 0.000061 seconds >=20 > Here's a look at the perf: >=20 > Unpatched: > 0.59% [.] AllocSetAlloc > 0.51% [.] hash_search_with_hash_value > 0.47% [.] base_yyparse >=20 > Patched: > 0.63% [.] AllocSetAlloc > 0.52% [.] hash_search_with_hash_value > 0.44% [.] base_yyparse >=20 > (*) select 1, 2, 3 from foo where a =3D 1 >=20 > Is there a more relevant benchmark I could use? Thought I=E2=80=99d share a few more numbers I collected to analyze the = parser size increase over releases. * gram.o text bytes is from the output of `size gram.o`. * compiled with -O3 version gram.o text bytes %change gram.c bytes %change 9.6 534010 - 2108984 - 10 582554 9.09 2258313 7.08 11 584596 0.35 2313475 2.44 12 590957 1.08 2341564 1.21 13 590381 -0.09 2357327 0.67 14 600707 1.74 2428841 3.03 15 633180 5.40 2495364 2.73 16 653464 3.20 2575269 3.20 17-sqljson 672800 2.95 2709422 3.97 So if we put SQL/JSON (including JSON_TABLE()) into 17, we end up with a = gram.o 2.95% larger than v16, which granted is a somewhat larger bump, = though also smaller than with some of recent releases. > -- Thanks, Amit Langote EDB: http://www.enterprisedb.com= --Apple-Mail=_125D3C3B-CDC8-4BE8-BF96-7053596975DC Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 On Nov 16, = 2023, at 17:48, Amit Langote <amitlangote09@gmail.com> = wrote:
On Thu, Nov 16, 2023 at = 2:11=E2=80=AFAM Andres Freund <andres@anarazel.de> = wrote:
On 2023-11-15 22:00:41 +0900, Amit = Langote wrote:
This= causes a nontrivial increase in the size of the parser (~5% in = an
optimized build here), I wonder if we can do = better.

Hmm, sorry if I sound ignorant but what do = you mean by the parser here?

gram.o, in an optimized = build.

I can see that the byte-size of = gram.o increases by 1.66% after the
above additions  (1.72% with = previous versions).

I'm not sure anymore how I = measured it, but if you just looked at the total
file size, that = might not show the full gain, because of debug symbols
etc. You can = use the size command to look at just the code and data = size.

$ size /tmp/gram.*
  text =    data     bss =     dec     hex filename
= 661808       0 =       0  661808   a1930 = /tmp/gram.o.unpatched
672800       0 =       0  672800   a4420 = /tmp/gram.o.patched

That's still a 1.66% increase in the code = size:

(672800 - 661808) / 661808 % =3D 1.66

As for gram.c, = the increase is a bit larger:

$ ll /tmp/gram.*
-rw-rw-r--. 1 = amit amit 2605925 Nov 16 16:18 /tmp/gram.c.unpatched
-rw-rw-r--. 1 = amit amit 2709422 Nov 16 16:22 /tmp/gram.c.patched

(2709422 - = 2605925) / 2605925 % =3D 3.97

I've also checked
using log_parser_stats that there = isn't much slowdown in the
raw-parsing = speed.

What does "isn't much slowdown" mean in = numbers?

Sure, the benchmark I used measured the = elapsed time (using
log_parser_stats) of parsing a simple select = statement (*) averaged
over 10000 repetitions of the same query = performed with `psql -c`:

Unpatched: 0.000061 seconds
Patched: = 0.000061 seconds

Here's a look at the perf:

Unpatched:
=   0.59%  [.] AllocSetAlloc
  0.51% =  [.] hash_search_with_hash_value
  0.47%  [.] = base_yyparse

Patched:
  0.63%  [.] = AllocSetAlloc
  0.52%  [.] = hash_search_with_hash_value
  0.44%  [.] = base_yyparse

(*) select 1, 2, 3 from foo where a =3D 1

Is = there a more relevant benchmark I could = use?

Thought I=E2=80=99d = share a few more numbers I collected to analyze the parser size increase = over releases.

* gram.o text bytes is from the = output of `size gram.o`.
* compiled with = -O3

version     gram.o = text bytes  %change  gram.c bytes =  %change

9.6         = 534010             -       =  2108984       -
10       =    582554             9.09 =     2258313       7.08
11   =        584596           =   0.35     2313475       2.44
12 =          590957         =     1.08     2341564       = 1.21
13          590381     =        -0.09     2357327     =   0.67
14          600707   =           1.74     2428841   =     3.03
15          633180 =             5.40     2495364 =       2.73
16         =  653464             3.20   =   2575269       3.20
17-sqljson =  672800             2.95   =   2709422       = 3.97

So if we put SQL/JSON = (including JSON_TABLE()) into 17, we end up with a gram.o 2.95% larger = than v16, which granted is a somewhat larger bump, though also smaller = than with some of recent releases.


--
Thanks, Amit = Langote
EDB: = http://www.enterprisedb.com
= --Apple-Mail=_125D3C3B-CDC8-4BE8-BF96-7053596975DC--