PostgreSQL跨关联表按最新更新时间排序分页查询方案优化咨询

场景说明 #

table0与table1为一对多关联关系，两张表可单独更新。需求为基于两张表的最新更新时间，查询最近更新的table0数据，且查询需支持分页。

单表查询table0的SQL #

SELECT
  "table_0"."id",
  "table_0"."short_name"
FROM
  "table_0"
ORDER BY
  "table_0"."updated_at" DESC
LIMIT
  10 OFFSET 0;

表数据 #

table_0数据 #

table_1数据 #

预期输出 #

自行编写的SQL（含测试数据） #

-- 用于复现场景的SQL语句
-- CREATE TABLE table_0(id TEXT, short_name TEXT, updatedAt INT);
-- INSERT INTO table_0 VALUES('t0_1', 'Name1', 46);
-- INSERT INTO table_0 VALUES('t0_2', 'Name2', 57);
-- INSERT INTO table_0 VALUES('t0_3', 'Name3', 93);

-- CREATE TABLE table_1(id TEXT, table_0_id TEXT, updatedAt INT);
-- INSERT INTO table_1 VALUES('t1_7', 't0_2', 94);
-- INSERT INTO table_1 VALUES('t1_6', 't0_3', 92);
-- INSERT INTO table_1 VALUES('t1_5', 't0_3', 67);
-- INSERT INTO table_1 VALUES('t1_4', 't0_1', 61);
-- INSERT INTO table_1 VALUES('t1_3', 't0_1', 59);
-- INSERT INTO table_1 VALUES('t1_2', 't0_2', 58);
-- INSERT INTO table_1 VALUES('t1_1', 't0_1', 47);

SELECT * FROM table_0;
SELECT * FROM table_1;

WITH "table_1_max" AS (
  SELECT
    DISTINCT "table_1"."table_0_id",
    MAX("table_1"."updatedat") AS "updatedat"
  FROM
    "table_1"
  GROUP BY
    "table_1"."table_0_id"
  ORDER BY
    "updatedat" DESC
)
SELECT
  "table_0"."id",
  "table_0"."short_name",
  CASE
    WHEN "table_1_max"."updatedat" > "table_0"."updatedat" THEN "table_1_max"."updatedat"
    ELSE "table_0"."updatedat"
  END AS "updatedat"
FROM
  "table_0"
  INNER JOIN "table_1_max" ON "table_1_max"."table_0_id" = "table_0"."id"
ORDER BY
  "updatedat" DESC,
  "table_0"."short_name" ASC
LIMIT
  10 OFFSET 0;

优化建议 #

1. 移除CTE中的冗余排序：table_1_max里的ORDER BY "updatedat" DESC对后续关联无意义，只会增加计算开销，直接删除即可。
2. 用GREATEST简化逻辑：可以用GREATEST("table_1_max"."updatedat", "table_0"."updatedat")替代CASE语句，代码更简洁，多数数据库对该函数的优化效果更好。
3. 兼容无关联记录的table0数据：当前使用INNER JOIN会过滤掉没有对应table1记录的table0数据。如果需求需要保留这类数据，应改为LEFT JOIN，同时用COALESCE处理NULL值，比如GREATEST(COALESCE("table_1_max"."updatedat", 0), "table_0"."updatedat")（若updated_at是日期类型，默认值换成'1970-01-01'即可）。
4. 索引优化：
   • 给table1建立(table_0_id, updatedat DESC)联合索引：CREATE INDEX idx_table1_table0id_updatedat ON table_1(table_0_id, updatedat DESC);，计算MAX(updatedat)时可直接利用索引，避免全表扫描。
   • 给table0的updatedat单独建索引；若数据量极大，建议用keyset分页替代OFFSET，避免大OFFSET带来的性能损耗（比如用WHERE updatedat < 上一页最大时间来分页）。

优化后的SQL示例 #

WITH "table_1_max" AS (
  SELECT
    "table_0_id",
    MAX("updatedat") AS "updatedat"
  FROM
    "table_1"
  GROUP BY
    "table_0_id"
)
SELECT
  "table_0"."id",
  "table_0"."short_name",
  GREATEST(COALESCE("table_1_max"."updatedat", 0), "table_0"."updatedat") AS "updatedat"
FROM
  "table_0"
  LEFT JOIN "table_1_max" ON "table_1_max"."table_0_id" = "table_0"."id"
ORDER BY
  "updatedat" DESC,
  "table_0"."short_name" ASC
LIMIT 10 OFFSET 0;

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