performance - 了解 Postgres 9.5 中的 json_agg 性能

Question

我试图了解我在类似的数据库查询之间看到的巨大速度差异，并且我希望对为什么某些聚合比其他聚合慢得多的原因有一些了解。

我注意到一个简单的文档检索查询存在一些速度问题，其中很大一部分似乎是 json_agg 函数:

SELECT containers.*, json_agg(content_items.*) as items FROM containers
INNER JOIN content_items ON containers.id = content_items.container_id 
GROUP BY containers.id 
ORDER BY containers.order_date DESC, containers.id DESC 
LIMIT 25 OFFSET 0;

显示总查询时间约为 500 毫秒，其中超过 400 毫秒用于聚合步骤:

GroupAggregate  (cost=11921.58..12607.34 rows=17540 width=1553) (actual time=78.818..484.071 rows=17455 loops=1)

只需将 json_agg 切换为 array_agg 即可将总时间降低到 150 毫秒的范围内，尽管仍有大约一半时间用于聚合:

GroupAggregate  (cost=11921.58..12607.34 rows=17540 width=1553) (actual time=81.975..147.207 rows=17455 loops=1)

在没有分组或聚合的情况下执行查询会使总时间减少到 25 毫秒，尽管这将返回可变数量的 containers，具体取决于每个 content_items 的数量。

json_agg 是否有理由施加这样的惩罚？是否有一种高效的方法来检索一定数量的 container 行及其所有 content_items，并简单地在应用程序层中聚合？

Answer 1

您可以执行两个查询:第一个查询将获取适当的容器，已排序并限制为 25 个。第二个查询将使用 where-in 子句获取 content_items。然后应用程序可以过滤内容并将其映射到适当的容器中。