PostgreSQL中高效查询一对零或多关系的性能优化咨询

我在PostgreSQL中处理复杂查询时，使用以下表结构：

CREATE TABLE primari (
  id   BIGINT NOT NULL PRIMARY KEY,
  name TEXT   NOT NULL
);

CREATE TABLE secondary (
  primary_id BIGINT NOT NULL references primari (id),
  id         BIGINT NOT NULL PRIMARY KEY,
  name       TEXT   NOT NULL
);

CREATE TABLE tertiary (
  primary_id BIGINT NOT NULL references primari (id),
  id         BIGINT NOT NULL PRIMARY KEY,
  name       TEXT   NOT NULL
);

所有子表与主表primari都是一对零或多的关系，主表记录可以关联零条、多条secondary或tertiary记录，且需要从每个表中获取多列数据。我想要实现类似以下伪SQL的查询效果：

SELECT
  primari.*,
  ARRAY_AGG(secondary.*) WHERE secondary.id = primari.id,
  ARRAY_AGG(tertiary.*) WHERE tertiary.id = primari.id
FROM primari
WHERE id IN (..)
AND (..);

我知道jOOQ的multiset功能可以把查询逻辑转移到数据库层，一次性获取所有数据，但PostgreSQL本身不原生支持multiset。我尝试了两种实现方式：

方案1：标量子查询嵌入SELECT（模拟multiset） #

SELECT
  primari.*,
  (
    SELECT
      -- 注：这是jOOQ multiset生成的逻辑
      JSONB_PRETTY(COALESCE(JSONB_AGG(JSONB_BUILD_OBJECT('id', s.id, 'primary_id', s.primary_id, 'name', s.name)), JSONB_BUILD_ARRAY()))
    FROM (
      SELECT secondary.* FROM secondary WHERE secondary.primary_id = primari.id
    ) AS s
  ) AS secondaries,
  (
    SELECT
      JSONB_PRETTY(COALESCE(JSONB_AGG(JSONB_BUILD_OBJECT('id', t.id, 'primary_id', t.primary_id, 'name', t.name)), JSONB_BUILD_ARRAY()))
    FROM (
      SELECT tertiary.* FROM tertiary WHERE tertiary.primary_id = primari.id
    ) AS t
  ) AS tertiaries
FROM primari
WHERE primari.id IN (..);

这种方式在测试小数据集时性能不错，但数据量增加到数千条时性能急剧下降。通过EXPLAIN ANALYZE发现，主表的每一行都会触发一次子查询，属于N+1查询问题。

方案2：先聚合子表再关联主表 #

SELECT
  primari.*,
  JSONB_PRETTY(COALESCE(secondary_sq.secondaries, JSONB_BUILD_ARRAY())) AS secondaries,
  JSONB_PRETTY(COALESCE(tertiary_sq.tertiaries, JSONB_BUILD_ARRAY())) AS tertiaries
FROM primari
LEFT OUTER JOIN (
  SELECT
    secondary.primary_id,
    COALESCE(JSONB_AGG(JSONB_BUILD_OBJECT('id', id, 'primary_id', primary_id, 'name', name)), JSONB_BUILD_ARRAY()) AS secondaries
  FROM secondary
  WHERE primary_id IN (..)
  GROUP BY primary_id
) AS secondary_sq
ON secondary_sq.primary_id = primari.id
LEFT OUTER JOIN (
  SELECT
    tertiary.primary_id,
    COALESCE(JSONB_AGG(JSONB_BUILD_OBJECT('id', id, 'primary_id', primary_id, 'name', name)), JSONB_BUILD_ARRAY()) AS tertiaries
  FROM tertiary
  WHERE primary_id IN (..)
  GROUP BY primary_id
) AS tertiary_sq
ON tertiary_sq.primary_id = primari.id
WHERE primari.id IN (..);

这种方案扩展性更好，但当过滤的ID数量过大时，性能依然会下降。

目前所有表都已针对过滤条件建立了索引，子表都有以primary_id为首列的索引。想请教：我是否存在思路上的误区？有没有更优的实现方案？

内容的提问来源于stack exchange，提问作者simonsays