PostgreSQL中带排序CTE的LIMIT 1查询结果是否有返回保证？

问题背景 #

构造了名为sorted的CTE，按以下规则升序排序：

1. 非空整数sort_1
2. 布尔值sort_2（空值后置）
3. 非空时间戳sort_3

CTE结果示例：

id | sort_1 | sort_2 |         sort_3         | condition_1 | condition_2
----+--------+--------+------------------------+-------------+-------------
  6 |      1 |        | 2022-12-06 00:00:00+00 | f           | t
  4 |      2 | f      | 2022-12-04 00:00:00+00 | t           | f
  5 |      2 |        | 2022-12-05 00:00:00+00 | f           | f
  ... Lots of other rows ...
  1 |    997 | t      | 2022-12-01 00:00:00+00 | f           | f
  3 |    997 | t      | 2022-12-03 00:00:00+00 | f           | t
  2 |    998 | f      | 2022-12-02 00:00:00+00 | t           | t

给定查询语句：

WITH sorted AS (
  -- Create CTE Here ---
  -- ...
  ORDER BY sort_1, sort_2 NULLS LAST, sort_3
)
(SELECT id FROM sorted WHERE condition_1 = 't' LIMIT 1)
UNION
(SELECT id FROM sorted WHERE condition_2 = 't' LIMIT 1);

核心问题 #

1. 无论sorted CTE返回多少行，是否总能得到满足条件且在排序后CTE中最先出现的id（如4和6）？是否可能返回其他满足条件的行（如id为2的行也满足condition_1='t'）？
2. PostgreSQL会从排序后的CTE顶部扫描吗？能否知晓扫描方式？
3. 若要保证每个子查询的排序顺序，是否需要在每个后续查询中重复ORDER BY子句？

解答 #

1. 原查询无法保证返回排序后最先出现的满足条件的ID #

不能确保总能拿到排序后CTE中最靠前的满足条件的ID，完全有可能返回像id=2这类在CTE中靠后但满足条件的行。

原因在于：PostgreSQL中，CTE的ORDER BY仅定义了CTE自身的输出顺序，但当后续从CTE查询时，数据库优化器不会默认沿用这个顺序。优化器会根据数据分布、统计信息等选择最高效的执行路径，比如直接从底层表中筛选满足条件的行，而不是先遍历已排序的CTE结果。

2. 扫描方式不确定，可通过执行计划查看 #

PostgreSQL的扫描方式没有固定规则，完全由优化器决定。你可以通过执行EXPLAIN ANALYZE命令查看具体的执行计划，确认当前查询的扫描逻辑，但这个计划可能随着数据量、数据分布的变化而改变。

3. 必须在每个子查询中重复ORDER BY才能保证顺序 #

要确保每个子查询都从排序后的CTE顶部开始扫描，拿到最先出现的满足条件的ID，必须在每个子查询中显式添加与CTE一致的ORDER BY子句。

修正后的查询示例：

WITH sorted AS (
  -- 原CTE定义，包含排序逻辑
  ORDER BY sort_1, sort_2 NULLS LAST, sort_3
)
(SELECT id FROM sorted WHERE condition_1 = 't' ORDER BY sort_1, sort_2 NULLS LAST, sort_3 LIMIT 1)
UNION
(SELECT id FROM sorted WHERE condition_2 = 't' ORDER BY sort_1, sort_2 NULLS LAST, sort_3 LIMIT 1);

由于CTE已经按指定顺序排好，子查询的ORDER BY也可以直接复用CTE的排序逻辑，确保优化器会按照CTE的排序顺序来筛选数据。

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