Re: to_jsonb performance on array aggregated correlated subqueries

Hi,

On 2022-08-12 18:49:58 +0000, Nico Heller wrote:
> WITH aggregation(
>     SELECT
>            a.*,
>           (SELECT array_agg(b.*) FROM b WHERE b.a_id = a.id) as "bs",
>           (SELECT array_agg(c.*) FROM c WHERE c.a_id = a.id) as "cs",
>           (SELECT array_agg(d.*) FROM d WHERE d.a_id = a.id) as "ds",
>           (SELECT array_agg(e.*) FROM d WHERE e.a_id = a.id) as "es"
>     FROM a WHERE a.id IN (<some big list, ranging from 20-180 entries)
> )
> SELECT to_jsonb(aggregation.*) as "value" FROM aggregation;

> Imagine that for each "a" there exists between 5-100 "b", "c", "d" and "e"
> which makes the result of this pretty big (worst case: around 300kb when
> saved to a text file).
> I noticed that adding the "to_jsonb" increases the query time by 100%, from
> 9-10ms to 17-23ms on average.

Could we see the explain?

Have you tried using json[b]_agg()?

> This may not seem slow at all but this query has another issue: on an AWS
> Aurora Serverless V2 instance we are running into a RAM usage of around
> 30-50 GB compared to < 10 GB when using a simple LEFT JOINed query when
> under high load (> 1000 queries / sec). Furthermore the CPU usage is quite
> high.

We can't say much about aurora. It's a heavily modified fork of postgres. Did
you reproduce this with vanilla postgres? And if so, do you have it in a form
that somebody could try out?

Greetings,

Andres Freund