Am 12.08.2022 um 21:02 schrieb Rick Otten:
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...
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!
