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 1ur4s1-000GfF-U4 for pgsql-hackers@arkaria.postgresql.org; Wed, 27 Aug 2025 01:22:39 +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 1ur4s0-00BkKm-4n for pgsql-hackers@arkaria.postgresql.org; Wed, 27 Aug 2025 01:22:36 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ur4rz-00BkKd-Mr for pgsql-hackers@lists.postgresql.org; Wed, 27 Aug 2025 01:22:36 +0000 Received: from mail-pj1-x1035.google.com ([2607:f8b0:4864:20::1035]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ur4rx-001vzE-1V for pgsql-hackers@lists.postgresql.org; Wed, 27 Aug 2025 01:22:35 +0000 Received: by mail-pj1-x1035.google.com with SMTP id 98e67ed59e1d1-32326793a85so4206839a91.1 for ; Tue, 26 Aug 2025 18:22:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1756257753; x=1756862553; darn=lists.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=SzdZZbYtZbAsuq0xjKATroWlJ4X8fUEEZRbWlyzLhlI=; b=GParjRk54jSs6YBEjneK8muCrD1983Towy8N8XAvRLqIA550eDc2O7Pz6W/SP/busE u8lzOMofINduaIJemOi3VagNrB0SxxYzLIZWLe9quseF/CBfNUt4CsTuhhYSW+7GLZC5 2d4UE9HR8yVPdbrY1olrx+m2j8m20wnVxFrgOszCCWj6UMux27ak9kbG85rRNacxM9/I bzfn4A+tUaTZcKgrRuI6VoymkWktGdxVZ+/81m5R2YdUVp6yN/CpuAxP5vFFCJECN0qb ZY0Xjudp7l78OZWNURj0cd4RyAd4URA55M69iLtQg87RJqCKYFh6m7m5XD68dydDUo/F JN3g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1756257753; x=1756862553; 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=SzdZZbYtZbAsuq0xjKATroWlJ4X8fUEEZRbWlyzLhlI=; b=Br7bZ2Lxu5dNxg13dvYdP1/lNtsa5FI4XZBJqCs5sZzsqpGtEkn7WvyEKL6h8sS03h ywHgtvJAPS0avG8EQe0LUzgrInu572laYMGn8hvluDQs4iIM/0TGINZRLrOAT7opDx/u Lk4v4eOxQslln2ijJuuOFOv3p1ybEPo+VDls/fxJFJX7NNkTwxMwRJWHWrrV/eRqnFG5 sRnLsJ5Twi4iQudY0xZCprX7OrVuwUBagTl/Jk27kC/mHmXE2ikssUjw8LWKbefnr782 nuLxahl+hdFsUHkTu4RGTkj9JC8qASzlUCflWS1SHl6uLy1Yk1xbgjrsOdswpVkA3MRb y2/Q== X-Forwarded-Encrypted: i=1; AJvYcCUXiGTlbN9CpaGPLEdia6O3GA8769MZH4StfjSF62bQoH8yoY1DDEwbsKJAe4lg3rHVAa6Seen2LB5YeqEj@lists.postgresql.org X-Gm-Message-State: AOJu0Ywcm/au3hzSQDOmQEgZk86x0AHwa9bKmijLPhXZnRjmcE9hC6zi 5C7vxvOi+1d5X0GgBmjJ/tsR21FrsfQ6fSZg6mnUl55Y3RLAgrNwKJQG X-Gm-Gg: ASbGncuNfP/zbdzn9pN9TPiSnLUH4aqjy8OlJuZnTnbOsNNgAch44RGoiZv/0h+ndEO QohzH/xv9FebBahhO+5gQwrqT2GziG7VHtiS7izSXEotXzkCCeZo0+vZuH2z5YfB8i7OalniMYZ qQRdottKMz85sPR69hxNdRA9BSUJ4QjrNKkCkPLCYAQbSzF1d6TU3tjKgcc1BYaYvylyqMTPyUR VLftR4N3vOsjxJ5xArTJI8NuqPtSQwqhyfokaJGCGEtynDVyeriL/EWsU8p8QYimmnmwCj776yi AAGn/zDD7CdqAF6OOXDbhU182+FqXo2TZqaOiGCvVbThyZqiwt7h/ahWN7hXtTFHoteU82Qn26C rkpIjlaSjyrymbmRlgBppVa44CjYmx/S0 X-Google-Smtp-Source: AGHT+IGSyBWN5/xht0X0kLUUMrFM6RyGUSqVDShn2oFbxYCFManzaFTveIlWX+cjTbq1cPT8q5Kp9w== X-Received: by 2002:a17:90b:1b4c:b0:312:26d9:d5b2 with SMTP id 98e67ed59e1d1-32515d26d9cmr22138798a91.0.1756257752628; Tue, 26 Aug 2025 18:22:32 -0700 (PDT) Received: from smtpclient.apple ([142.171.105.12]) by smtp.gmail.com with ESMTPSA id 98e67ed59e1d1-3276fde5be7sm467704a91.31.2025.08.26.18.22.30 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 26 Aug 2025 18:22:32 -0700 (PDT) From: Chao Li Message-Id: <2613D418-67E0-4DD8-BDA6-AB1BB04DB1A2@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_EA789E10-9CC0-4B65-BFCD-41CC487CA78B" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\)) Subject: Re: Making jsonb_agg() faster Date: Wed, 27 Aug 2025 09:22:17 +0800 In-Reply-To: <68A1061C-81A5-46AA-AFA9-48294908254C@gmail.com> Cc: jian he , pgsql-hackers@lists.postgresql.org To: Tom Lane References: <1060917.1753202222@sss.pgh.pa.us> <617666.1755277022@sss.pgh.pa.us> <654628.1755297629@sss.pgh.pa.us> <2129790.1755889894@sss.pgh.pa.us> <68A1061C-81A5-46AA-AFA9-48294908254C@gmail.com> X-Mailer: Apple Mail (2.3826.700.81) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_EA789E10-9CC0-4B65-BFCD-41CC487CA78B Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 >> On Aug 23, 2025, at 03:11, Tom Lane wrote: >>=20 >>=20 >> v2-0001 takes care of that, and also adopts your suggestion in [1] >> about not using two calls of pushJsonbValueScalar where one would do. >> I also did a bit more micro-optimization in appendKey, appendValue, >> appendElement to avoid redundant copying, because perf testing showed >> that appendElement is still a hot-spot for jsonb_agg. Patches 0002 >> and 0003 are unchanged. >>=20 >=20 >=20 A few more suggestions for pushJsonValue(): + /* If an object or array is pushed, recursively push its = contents */ + if (jbval->type =3D=3D jbvObject) { pushJsonbValue(pstate, WJB_BEGIN_OBJECT, NULL); for (i =3D 0; i < jbval->val.object.nPairs; i++) @@ -581,32 +607,29 @@ pushJsonbValue(JsonbParseState **pstate, = JsonbIteratorToken seq, pushJsonbValue(pstate, WJB_KEY, = &jbval->val.object.pairs[i].key); pushJsonbValue(pstate, WJB_VALUE, = &jbval->val.object.pairs[i].value); } - - return pushJsonbValue(pstate, WJB_END_OBJECT, NULL); + pushJsonbValue(pstate, WJB_END_OBJECT, NULL); + return; } To push WJB_BEGIN_OBJECT and WJB_END_OBJECT, we can directly call = pushJsonValueScalar(), because once entering pushJsonbValue, they will = meet the check of (seq !=3D WJB_ELEM && seq !=3D WJB_VALUE). Directly = calling pushJsonValueScalar() will saves one level of recursion. - if (jbval && (seq =3D=3D WJB_ELEM || seq =3D=3D WJB_VALUE) && = jbval->type =3D=3D jbvArray) + if (jbval->type =3D=3D jbvArray) { pushJsonbValue(pstate, WJB_BEGIN_ARRAY, NULL); for (i =3D 0; i < jbval->val.array.nElems; i++) { pushJsonbValue(pstate, WJB_ELEM, = &jbval->val.array.elems[i]); } - - return pushJsonbValue(pstate, WJB_END_ARRAY, NULL); + pushJsonbValue(pstate, WJB_END_ARRAY, NULL); + return; } Same thing for pushing WJB_BEGIN_ARRAY and WJB_END_ARRAY. And for pushJsonbValueScalar(): - (*pstate)->size =3D 4; + ppstate->size =3D 4; /* initial = guess at array size */ Can we do lazy allocation? Initially assume size =3D 0, only allocate = memory when pushing the first element? This way, we won=E2=80=99t = allocate memory for empty objects and arrays.=20 Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_EA789E10-9CC0-4B65-BFCD-41CC487CA78B Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
On = Aug 23, 2025, at 03:11, Tom Lane <tgl@sss.pgh.pa.us> = wrote:


v2-0001 takes care of that, and also adopts = your suggestion in [1]
about not using two calls of = pushJsonbValueScalar where one would do.
I also did a bit more = micro-optimization in appendKey, appendValue,
appendElement to avoid = redundant copying, because perf testing showed
that appendElement is = still a hot-spot for jsonb_agg.  Patches 0002
and 0003 are = unchanged.



A few more suggestions for = pushJsonValue():

+       /* = If an object or array is pushed, recursively push its contents = */
+       if (jbval->type =3D=3D = jbvObject)
        {
  =               pushJsonbValue(pstate, = WJB_BEGIN_OBJECT, NULL);
          =       for (i =3D 0; i < jbval->val.object.nPairs; = i++)
@@ -581,32 +607,29 @@ pushJsonbValue(JsonbParseState = **pstate, JsonbIteratorToken seq,
        =                 = pushJsonbValue(pstate, WJB_KEY, = &jbval->val.object.pairs[i].key);
      =                   = pushJsonbValue(pstate, WJB_VALUE, = &jbval->val.object.pairs[i].value);
    =             }
-
- =               return = pushJsonbValue(pstate, WJB_END_OBJECT, NULL);
+     =           pushJsonbValue(pstate, = WJB_END_OBJECT, NULL);
+           =     return;
        = }

To push WJB_BEGIN_OBJECT and WJB_END_OBJECT, = we can directly call pushJsonValueScalar(), because once entering = pushJsonbValue, they will meet the check of  (seq !=3D WJB_ELEM = && seq !=3D WJB_VALUE). Directly calling pushJsonValueScalar() = will saves one level of recursion.

- =       if (jbval && (seq =3D=3D WJB_ELEM || seq =3D=3D= WJB_VALUE) && jbval->type =3D=3D jbvArray)
+ =       if (jbval->type =3D=3D = jbvArray)
        {
  =               pushJsonbValue(pstate, = WJB_BEGIN_ARRAY, NULL);
          =       for (i =3D 0; i < jbval->val.array.nElems; = i++)
                = {
                =         pushJsonbValue(pstate, WJB_ELEM, = &jbval->val.array.elems[i]);
      =           }
-
-   =             return pushJsonbValue(pstate, = WJB_END_ARRAY, NULL);
+           =     pushJsonbValue(pstate, WJB_END_ARRAY, NULL);
+ =               = return;
        = }

Same thing for pushing WJB_BEGIN_ARRAY and = WJB_END_ARRAY.

And = for pushJsonbValueScalar():

-   =                     =         (*pstate)->size =3D 4;
+   =                     =         ppstate->size =3D 4;     =  /* initial guess at array size */

Can we = do lazy allocation? Initially assume size =3D 0, only allocate memory = when pushing the first element? This way, we won=E2=80=99t allocate = memory for empty objects and = arrays. 


Best = regards,
--
Chao Li (Evan)
HighGo Software = Co., Ltd.
https://www.highgo.com/




= --Apple-Mail=_EA789E10-9CC0-4B65-BFCD-41CC487CA78B--