Re: to_jsonb performance on array aggregated correlated subqueries

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) 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;

- You do have an index on `b.a_id` and `c.a_id`, etc... ?  You didn't say...

- Are you sure it is the `to_jsonb` that is making this query slow?

- 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?