PostgreSQL：如何用单SQL实现查询结果不足时自动补全？

需求回顾 #

你需要优先获取满足以下条件的订阅记录（最多$2条）：

• 从未同步（last_sync_at IS NULL），或同步时间早于更新时间，且更新时间早于$1
• 按last_sync_at NULLS FIRST排序

如果上述查询返回的记录数不足$2，则从所有订阅中按相同排序规则补全剩余数量，且不能重复获取已选中的记录。

可行的单条SQL实现 #

可以通过CTE（公共表表达式）实现这个逻辑，以下是完整SQL：

WITH first_batch AS (
    -- 优先获取符合条件的记录
    SELECT *
    FROM subscriptions
    WHERE (last_sync_at IS NULL OR last_sync_at < updated_at) 
      AND updated_at < $1
    ORDER BY last_sync_at NULLS FIRST
    LIMIT $2
),
first_batch_count AS (
    -- 统计优先批次的记录数
    SELECT COUNT(*) AS total FROM first_batch
)
-- 合并优先批次和补全批次
SELECT * FROM first_batch
UNION ALL
-- 从剩余记录中补全不足的数量
SELECT s.*
FROM subscriptions s
LEFT JOIN first_batch fb ON s.id = fb.id
WHERE fb.id IS NULL  -- 排除已在优先批次中的记录
ORDER BY s.last_sync_at NULLS FIRST
LIMIT (SELECT $2 - total FROM first_batch_count);

逻辑说明 #

1. first_batch：先执行你的第一个查询，拿到最多$2条优先记录。
2. first_batch_count：统计优先批次的实际返回行数，计算需要补全的数量（$2 - total）。
3. 合并结果：先返回所有优先批次的记录，再从剩余订阅中按规则取需要补全的数量，用UNION ALL避免不必要的去重操作。

性能分析 #

• 单条SQL的优势：减少了应用程序与数据库之间的网络往返次数，在跨网络部署的场景下（比如应用和数据库不在同一机房），能显著降低延迟。
• 注意事项：
  • 如果subscriptions表数据量很大，需要确保last_sync_at、updated_at字段有合适的索引（比如CREATE INDEX idx_subscriptions_sync_updated ON subscriptions(last_sync_at, updated_at);），否则排序和过滤会很慢。
  • 用LEFT JOIN替代NOT IN来排除已选记录，能避免NOT IN在处理NULL值时的潜在问题，同时性能更稳定。

你原有SQL的问题 #

1. 子查询中COUNT(*) as nb_rows_selected的用法错误：GROUP BY id会让每条记录都返回1，无法统计批次总数量，且UNION的两个子查询不能互相引用对方的字段。
2. 第二个子查询中last_sy是拼写错误，应为last_sync_at。
3. 没有排除已在优先批次中选中的记录，会导致重复数据。

代码实现 vs 单条SQL #

• 代码实现：逻辑更直观，容易调试，且如果优先批次已经取满$2条，可以直接跳过补全查询，节省数据库资源。
• 单条SQL：减少网络开销，适合低延迟要求的场景，但无论优先批次是否取满，都会执行完整的CTE逻辑。

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