Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1oMa0D-0002Ku-TQ
	for pgsql-performance@arkaria.postgresql.org; Fri, 12 Aug 2022 19:07:26 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1oMa0C-0005Z8-SQ
	for pgsql-performance@arkaria.postgresql.org; Fri, 12 Aug 2022 19:07:24 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <nico.heller@posteo.de>)
	id 1oMa0C-0005XZ-Hk
	for pgsql-performance@lists.postgresql.org; Fri, 12 Aug 2022 19:07:24 +0000
Received: from mout02.posteo.de ([185.67.36.66])
	by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <nico.heller@posteo.de>)
	id 1oMa0A-0005sY-0r
	for pgsql-performance@lists.postgresql.org; Fri, 12 Aug 2022 19:07:24 +0000
Received: from submission (posteo.de [185.67.36.169])
	by mout02.posteo.de (Postfix) with ESMTPS id 1584A240104
	for <pgsql-performance@lists.postgresql.org>;
 Fri, 12 Aug 2022 21:07:20 +0200 (CEST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=posteo.de; s=2017;
	t=1660331241; bh=UnoajAMVo+4Uy1KazFkvWngaYqzgBlPvS3yeOlRWFBg=;
	h=Date:Subject:To:Cc:From:From;
	b=jDDwpcogRJgSCe5oG1tKVAZvsuqPBcgmyeXua48u4YVa4xzF2Bv563LQyi+QC1k7P
	 vCr0Bia73TQAku+BenCVH6jSz/HkIrzy6oDTdMnCKCNXDVLlYZoaK4PVRwSZo9uSEL
	 /X/UO6Ynr5jwgg6Z3Sb3hSPFdxqYm9ZLv6w3ROSscOH4pbWD12Cup+Sd7pYLyRA52A
	 Vey76IHxbPGSbMEkz36xP75PBEb2sZ5KKS/f8DAsh67UDRViy6ibNkQZ5JBzOqSX8a
	 MDESnK33vbdTuvQyrwMLWg8lSuOCzQDuhzLcVbPdZypnvlUZi2CRGIwhCa9dHI9o0W
	 7jyGgobXc0KsQ==
Received: from customer (localhost [127.0.0.1])
	by submission (posteo.de) with ESMTPSA id 4M4Cpw0vDXz9rxM;
	Fri, 12 Aug 2022 21:07:20 +0200 (CEST)
Content-Type: multipart/alternative;
 boundary="------------Ec0OF000wCl6cOid3xdoYDZO"
Message-ID: <ad15e35d-a1d6-1aa3-b07f-93fe42f8a2e8@posteo.de>
Date: Fri, 12 Aug 2022 19:07:20 +0000
MIME-Version: 1.0
Subject: Re: to_jsonb performance on array aggregated correlated subqueries
To: Rick Otten <rottenwindfish@gmail.com>
References: <1eac0b83-217d-94d2-cde2-db74ae3fbe86@posteo.de>
 <CAMAYy4Ln3K1o88r3hRyi_+66-LGtJpfMW-6uAOSXivyXJc0Wbg@mail.gmail.com>
Cc: pgsql-performance@lists.postgresql.org
From: Nico Heller <nico.heller@posteo.de>
In-Reply-To: 
 <CAMAYy4Ln3K1o88r3hRyi_+66-LGtJpfMW-6uAOSXivyXJc0Wbg@mail.gmail.com>
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Archived-At: 
 <https://www.postgresql.org/message-id/ad15e35d-a1d6-1aa3-b07f-93fe42f8a2e8%40posteo.de>
Precedence: bulk

This is a multi-part message in MIME format.
--------------Ec0OF000wCl6cOid3xdoYDZO
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Am 12.08.2022 um 21:02 schrieb Rick Otten:

>
>
> On Fri, Aug 12, 2022 at 2:50 PM Nico Heller <nico.heller@posteo.de> wrote:
>
>     Good day,
>
>     consider the following query:
>
>     WITH aggregation(
>          SELECT
>                 a.*,
>                (SELECT array_agg(b.*) FROM b WHERE b.a_id = a.id
>     <http://a.id>) as "bs",
>                (SELECT array_agg(c.*) FROM c WHERE c.a_id = a.id
>     <http://a.id>) as "cs",
>                (SELECT array_agg(d.*) FROM d WHERE d.a_id = a.id
>     <http://a.id>) as "ds",
>                (SELECT array_agg(e.*) FROM d WHERE e.a_id = a.id
>     <http://a.id>) as "es"
>          FROM a WHERE a.id <http://a.id> IN (<some big list, ranging
>     from 20-180 entries)
>     )
>     SELECT to_jsonb(aggregation.*) as "value" FROM aggregation;
>
>
> - You do have an index on `b.a_id` and `c.a_id`, etc... ?  You didn't 
> say...
Yes there are indices on all referenced columns of the subselect (they 
are all primary keys anyway)
> - Are you sure it is the `to_jsonb` that is making this query slow?
Yes, EXPLAIN ANALYZE shows a doubling of execution time - I don't have 
numbers on the memory usage difference though
>
> - Since you are serializing this for easy machine readable consumption 
> outside of the database, does it make a difference if you use 
> `to_json` instead?
>
Using to_json vs. to_jsonb makes no difference in regards to runtime, I 
will check if the memory consumption is different on monday - thank you 
for the idea!

--------------Ec0OF000wCl6cOid3xdoYDZO
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Am 12.08.2022 um 21:02 schrieb Rick Otten:<br>
    </p>
    <blockquote type="cite"
cite="mid:CAMAYy4Ln3K1o88r3hRyi_+66-LGtJpfMW-6uAOSXivyXJc0Wbg@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr"><br>
        </div>
        <br>
        <div class="gmail_quote">
          <div dir="ltr" class="gmail_attr">On Fri, Aug 12, 2022 at 2:50
            PM Nico Heller &lt;<a href="mailto:nico.heller@posteo.de"
              moz-do-not-send="true" class="moz-txt-link-freetext">nico.heller@posteo.de</a>&gt;
            wrote:<br>
          </div>
          <blockquote class="gmail_quote" style="margin:0px 0px 0px
            0.8ex;border-left:1px solid
            rgb(204,204,204);padding-left:1ex">Good day,<br>
            <br>
            consider the following query:<br>
            <br>
            WITH aggregation(<br>
                 SELECT<br>
                        a.*,<br>
                       (SELECT array_agg(b.*) FROM b WHERE b.a_id = <a
              href="http://a.id" rel="noreferrer" target="_blank"
              moz-do-not-send="true">a.id</a>) as "bs",<br>
                       (SELECT array_agg(c.*) FROM c WHERE c.a_id = <a
              href="http://a.id" rel="noreferrer" target="_blank"
              moz-do-not-send="true">a.id</a>) as "cs",<br>
                       (SELECT array_agg(d.*) FROM d WHERE d.a_id = <a
              href="http://a.id" rel="noreferrer" target="_blank"
              moz-do-not-send="true">a.id</a>) as "ds",<br>
                       (SELECT array_agg(e.*) FROM d WHERE e.a_id = <a
              href="http://a.id" rel="noreferrer" target="_blank"
              moz-do-not-send="true">a.id</a>) as "es"<br>
                 FROM a WHERE <a href="http://a.id" rel="noreferrer"
              target="_blank" moz-do-not-send="true">a.id</a> IN
            (&lt;some big list, ranging from 20-180 entries)<br>
            )<br>
            SELECT to_jsonb(aggregation.*) as "value" FROM aggregation;<br>
            <br>
          </blockquote>
          <div><br>
          </div>
          <div>- You do have an index on `b.a_id` and `c.a_id`, etc...
            ?  You didn't say...</div>
        </div>
      </div>
    </blockquote>
    Yes there are indices on all referenced columns of the subselect
    (they are all primary keys anyway)<br>
    <blockquote type="cite"
cite="mid:CAMAYy4Ln3K1o88r3hRyi_+66-LGtJpfMW-6uAOSXivyXJc0Wbg@mail.gmail.com">
      <div dir="ltr">
        <div class="gmail_quote">
          <div>- Are you sure it is the `to_jsonb` that is making this
            query slow?</div>
        </div>
      </div>
    </blockquote>
    Yes, EXPLAIN ANALYZE shows a doubling of execution time - I don't
    have numbers on the memory usage difference though<br>
    <blockquote type="cite"
cite="mid:CAMAYy4Ln3K1o88r3hRyi_+66-LGtJpfMW-6uAOSXivyXJc0Wbg@mail.gmail.com">
      <div dir="ltr">
        <div class="gmail_quote">
          <div><br>
          </div>
          <div>- Since you are serializing this for easy machine
            readable consumption outside of the database, does it make a
            difference if you use `to_json` instead?</div>
          <div><br>
          </div>
        </div>
      </div>
    </blockquote>
    <p>Using to_json vs. to_jsonb makes no difference in regards to
      runtime, I will check if the memory consumption is different on
      monday - thank you for the idea!<br>
    </p>
  </body>
</html>

--------------Ec0OF000wCl6cOid3xdoYDZO--