假设我有这两个表:作者(id,姓名,国家/地区)和出版物(id,姓名,年份,author_id)。
我最终想要得到的是:
作者 ID | 作者姓名 | 作者国家 | 作者出版物
其中,author_publications 是一个 JSON 数组,其中包含相应作者的所有出版物。
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
这个查询返回的正是我想要的。也就是说,当数据库实例繁忙时,性能会变得非常糟糕。JSON 聚合使查询速度慢 5-6 倍。
发布表中的外键(author_id)有一个索引。
有没有更好的方法来得到我想要的结果?
+-----------------------------------------------------------------------------------------------------------------------------------------------+
|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 |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
执行时间似乎花在了写入临时文件上,因为哈希聚合的哈希大小超过了
work_mem
timeshash_mem_multiplier
。您应该增加其中一个或两个参数,直到不再使用磁盘(但要注意“内存不足”情况)。