How to improve json_agg and json_build_object performance?

Question

I have table with 179,730 rows. This is very little compare to millions or billions of rows that database should be able to handle.

With simple query

-- 305ms
SELECT *
FROM my_table;

-- 300ms
SELECT t1.type
FROM my_table as t1
GROUP BY t1.type;

These take ~300ms which is reasonable for full scan of table.

But with json_agg and json_build_object the performance is surprisingly very slow

-- 5s 904ms
SELECT t1.type,
       json_agg(json_build_object(
                        'amount', t1.amount,
                        'text', t1.text,
                        'object_id', t1.object_id
                    )) AS members
FROM my_table as t1
GROUP BY t1.type;

From 305ms to 5s 904ms just by wanting to aggregate using json_agg?

How can I possibly improve the speed of this aggregation?

Answer 1

I figured it out. Actually the speed is very fast. The cause of increased in time is because I am fetching the data from remote place. The time elapsed is basically me "downloading" array of JSON. Execution in the server itself is very fast and reasonable as expected!