0

Let's say I have these two tables: author(id, name, country) and publication(id, name, year, author_id).

What I want to get in the end is:

author_id | author_name | author_country | author_publications

Where author_publications is a JSON array with all publications of the correspodning author.

SELECT author.id AS author_id,
       author.name AS author_name,
       author.country as author_country,
       JSONB_AGG(JSONB_BUILD_OBJECT(
                         'id',
                         publication.id,
                         'name',
                         publication.name,
                         'year',
                         publication.year
                     )) AS author_publications

FROM author
JOIN publication
ON author.id = publication.author_id
GROUP BY author.id

This query returns exactly what I want. That said, the performance gets pretty ugly when the database instance is busy. The JSON aggregation makes the query 5-6 times slower.

There is an index on the foreign key (author_id) in the publication table.

Is there a better way to get the result I want?

+-----------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
|HashAggregate  (cost=107.31..108.35 rows=83 width=116) (actual time=25.764..38.070 rows=83 loops=1)                                            |
|  Group Key: author.id                                                                                                                      |
|  Batches: 5  Memory Usage: 4400kB  Disk Usage: 240kB                                                                                          |
|  ->  Hash Join  (cost=4.87..88.64 rows=2490 width=197) (actual time=0.077..2.250 rows=2448 loops=1)                                           |
|        Hash Cond: (publication.author_id = author.id)                                                                                     |
|        ->  Seq Scan on publication  (cost=0.00..76.90 rows=2490 width=113) (actual time=0.008..1.115 rows=2448 loops=1)|
|        ->  Hash  (cost=3.83..3.83 rows=83 width=84) (actual time=0.062..0.063 rows=83 loops=1)                                                |
|              Buckets: 1024  Batches: 1  Memory Usage: 18kB                                                                                    |
|              ->  Seq Scan on author  (cost=0.00..3.83 rows=83 width=84) (actual time=0.006..0.040 rows=83 loops=1)  |
|Planning Time: 0.287 ms                                                                                                                        |
|Execution Time: 38.494 ms                                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------------------------------------+

5
  • That amount of slowdown is surprising. Did you try json_build_object and json_agg? Oct 5, 2023 at 7:17
  • I did, and it's still the same. That might be a symptom of some other issue with the instance (it's a managed cloud instance) but according to the cloud's statistics, this query sometimes takes many seconds. In general, would you say that this query is good enough given the purpose? It seems that the JSON/JSONB building part is most expensive as without it, it takes milliseconds.
    – Don Draper
    Oct 5, 2023 at 8:32
  • You might be suffering from network latency (if all queries are slow) or from a throttled disk or CPU. Common grievances in the cloud. Can you reproduce this with unmodified PostgreSQL? Oct 5, 2023 at 8:35
  • @LaurenzAlbe, I have posted the output from EXPLAIN ANALYZE (I executed this query locally while connected to the cloud instance from my IDE).
    – Don Draper
    Oct 5, 2023 at 9:09
  • Looks like it's not too bad with 29-50 ms per request but it could be that, as you suggested, the instance is lagging somehow.
    – Don Draper
    Oct 5, 2023 at 9:10

1 Answer 1

1

It seems like the execution time is spent writing temporary files because the size of the hash for the hash aggregate exceeds work_mem times hash_mem_multiplier. You should increase one or both of these parameters until no more disk is used (but beware of "out of memory" conditions).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.