From: Nico Heller <nico.heller@posteo.de>
To: Rick Otten <rottenwindfish@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: to_jsonb performance on array aggregated correlated subqueries
Date: Fri, 12 Aug 2022 19:07:20 +0000
Message-ID: <ad15e35d-a1d6-1aa3-b07f-93fe42f8a2e8@posteo.de> (raw)
In-Reply-To: <CAMAYy4Ln3K1o88r3hRyi_+66-LGtJpfMW-6uAOSXivyXJc0Wbg@mail.gmail.com>
References: <1eac0b83-217d-94d2-cde2-db74ae3fbe86@posteo.de>
<CAMAYy4Ln3K1o88r3hRyi_+66-LGtJpfMW-6uAOSXivyXJc0Wbg@mail.gmail.com>
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!
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-performance@postgresql.org
Cc: nico.heller@posteo.de, rottenwindfish@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: to_jsonb performance on array aggregated correlated subqueries
In-Reply-To: <ad15e35d-a1d6-1aa3-b07f-93fe42f8a2e8@posteo.de>
* 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