Re: to_jsonb performance on array aggregated correlated subqueries

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!