如何避免查询中的子查询？优化多表关联的最新值查询

问题背景 #

现有如下数据库Schema：MyDomainObject是可关联多个值的对象，同一事务中可修改一个或多个值，所有修改关联单个ChangeRecord。

CREATE TABLE MyDomainObject (
 domain_id serial PRIMARY KEY,
 domain_name text UNIQUE NOT NULL
);

CREATE TABLE ChangeRecord (
  change_id serial PRIMARY KEY,
  change_when timestamptz,
  change_source text NOT NULL,
  change_comment text
);

CREATE TABLE StringColumn (
  domain_id integer REFERENCES MyDomainObject,
  change_id integer REFERENCES ChangeRecord,
  column_name text NOT NULL, 
  str_value text NOT NULL,
  UNIQUE (domain_id, change_id, column_name)
);

CREATE TABLE IntegerColumn (
  domain_id integer REFERENCES MyDomainObject,
  change_id integer REFERENCES ChangeRecord,
  column_name text NOT NULL, 
  int_value text NOT NULL,
  UNIQUE (domain_id, change_id, column_name)
);

当前采用子查询方式查询一组字段的最新值、时间戳、来源元组：

SELECT
  mdo.domain_id, mdo.domain_name,
  ( SELECT (sc.str_value, cr.change_when, cr.change_source)
    FROM StringColumn sc JOIN ChangeRecord cr USING (change_id)
    WHERE column_name = 'some_field' and mdo.domain_id = sc.domain_id
    ORDER BY cr.change_when DESC LIMIT 1 
  ) AS some_field,
  ( SELECT (ic.int_value, cr.change_when, cr.change_source)
    FROM IntegerColumn ic JOIN ChangeRecord cr USING (change_id)
    WHERE column_name = 'another_field' and mdo.domain_id= ic.domain_id
    ORDER BY cr.change_when DESC LIMIT 1 
  ) AS another_field,
  ( SELECT (ic.int_value, cr.change_when, cr.change_source)
    FROM IntegerColumn ic JOIN ChangeRecord cr USING (change_id)
    WHERE column_name = 'yet_another_field' and mdo.domain_id= ic.domain_id
    ORDER BY cr.change_when DESC LIMIT 1 
  ) AS yet_another_field
FROM MyDomainObject mdo

担心数据集增大后，该查询因执行M×N次查询（M为MyDomainObject行数，N为需查询的字段数）而变慢，询问是否可以用Join优化提升性能。

优化方案：用Join+窗口函数替代子查询 #

完全可以通过Join结合窗口函数优化，避免逐行逐字段的子查询开销。核心思路是先批量筛选出每个字段对应domain_id的最新变更记录，再将结果与MyDomainObject关联。

最终优化查询 #

SELECT
  mdo.domain_id,
  mdo.domain_name,
  -- 提取some_field的元组值
  (MAX(CASE WHEN col.column_name = 'some_field' THEN col.value END),
   MAX(CASE WHEN col.column_name = 'some_field' THEN col.change_when END),
   MAX(CASE WHEN col.column_name = 'some_field' THEN col.change_source END)) AS some_field,
  -- 提取another_field的元组值
  (MAX(CASE WHEN col.column_name = 'another_field' THEN col.value END),
   MAX(CASE WHEN col.column_name = 'another_field' THEN col.change_when END),
   MAX(CASE WHEN col.column_name = 'another_field' THEN col.change_source END)) AS another_field,
  -- 提取yet_another_field的元组值
  (MAX(CASE WHEN col.column_name = 'yet_another_field' THEN col.value END),
   MAX(CASE WHEN col.column_name = 'yet_another_field' THEN col.change_when END),
   MAX(CASE WHEN col.column_name = 'yet_another_field' THEN col.change_source END)) AS yet_another_field
FROM MyDomainObject mdo
LEFT JOIN (
  -- 合并字符串列的最新记录
  SELECT domain_id, column_name, value, change_when, change_source
  FROM (
    SELECT
      sc.domain_id,
      sc.column_name,
      sc.str_value AS value,
      cr.change_when,
      cr.change_source,
      ROW_NUMBER() OVER (PARTITION BY sc.domain_id, sc.column_name ORDER BY cr.change_when DESC) AS rn
    FROM StringColumn sc
    JOIN ChangeRecord cr USING (change_id)
    WHERE sc.column_name IN ('some_field')
  ) AS latest_str
  WHERE rn = 1

  UNION ALL

  -- 合并整数列的最新记录
  SELECT domain_id, column_name, value, change_when, change_source
  FROM (
    SELECT
      ic.domain_id,
      ic.column_name,
      ic.int_value AS value,
      cr.change_when,
      cr.change_source,
      ROW_NUMBER() OVER (PARTITION BY ic.domain_id, ic.column_name ORDER BY cr.change_when DESC) AS rn
    FROM IntegerColumn ic
    JOIN ChangeRecord cr USING (change_id)
    WHERE ic.column_name IN ('another_field', 'yet_another_field')
  ) AS latest_int
  WHERE rn = 1
) AS col ON mdo.domain_id = col.domain_id
GROUP BY mdo.domain_id, mdo.domain_name
ORDER BY mdo.domain_id;

优化逻辑说明 #

1. 窗口函数筛选最新记录：用ROW_NUMBER()按(domain_id, column_name)分组，再按change_when倒序排序，取rn=1的行即为每个字段的最新变更。
2. 合并多列数据：通过UNION ALL合并字符串列和整数列的最新记录，避免多次扫描表。
3. 条件聚合转列：用CASE配合MAX()将行数据转成目标字段的元组，最后与MyDomainObject关联返回结果。

额外性能建议 #

• 为StringColumn创建复合索引：CREATE INDEX idx_str_col_domain_column ON StringColumn (domain_id, column_name, change_id);
• 为IntegerColumn创建复合索引：CREATE INDEX idx_int_col_domain_column ON IntegerColumn (domain_id, column_name, change_id);
  这两个索引可以大幅提升窗口函数的分组排序效率，进一步优化大数据集下的查询速度。

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