如何让PostgreSQL在排序后再计算列？

这个问题确实很常见——当你的SQL查询包含大量复杂计算列，同时需要按其中一个计算列排序并仅返回前100行时，PostgreSQL默认会先计算所有行的全部计算列，再排序取数，这在数据量大的时候会造成巨大的性能浪费。我来给你拆解下具体的解决方案：

先复现你的场景 #

首先按照你的测试数据准备：

-- 创建测试表
CREATE TABLE test_main(col1 INTEGER);

-- 插入10万条随机数据
DO $do$ BEGIN FOR r IN 1..100000 LOOP INSERT INTO test_main(col1) VALUES (trunc(random()*1000)); END LOOP; END $do$;

假设你的原始查询是这样的（包含多个计算列，按calc_col1排序取前100）：

SELECT
  col1,
  col1 * 2 AS calc_col1,
  sqrt(col1) AS calc_col2,
  col1 ^ 3 AS calc_col3,
  -- 更多复杂计算列...
  md5(col1::TEXT) AS calc_colN
FROM test_main
ORDER BY calc_col1
LIMIT 100;

用EXPLAIN ANALYZE查看执行计划会发现：PostgreSQL会先全表扫描，计算所有列的值，再对全部数据排序，最后取前100行。这显然做了很多无用的计算。

解决方案1：先取排序后的100行，再计算其他列 #

核心思路是拆分查询步骤：先只计算用于排序的列，取出前100行，再针对这100行计算其他复杂列。可以用子查询或者CTE实现：

子查询版本 #

SELECT
  sub.col1,
  sub.calc_col1,
  sqrt(sub.col1) AS calc_col2,
  sub.col1 ^ 3 AS calc_col3,
  md5(sub.col1::TEXT) AS calc_colN
FROM (
  -- 仅计算排序所需的列，取前100行
  SELECT
    col1,
    col1 * 2 AS calc_col1
  FROM test_main
  ORDER BY calc_col1
  LIMIT 100
) sub;

CTE版本（PostgreSQL 12+推荐） #

WITH top_100_rows AS (
  SELECT
    col1,
    col1 * 2 AS calc_col1
  FROM test_main
  ORDER BY calc_col1
  LIMIT 100
)
SELECT
  t.col1,
  t.calc_col1,
  sqrt(t.col1) AS calc_col2,
  t.col1 ^ 3 AS calc_col3,
  md5(t.col1::TEXT) AS calc_colN
FROM top_100_rows t;

这样执行计划会先处理内层子查询/CTE：扫描表计算排序列，排序后取100行，然后外层仅对这100行计算其他列，直接减少了99.9%的计算量。

解决方案2：给排序用的计算列创建索引（最优解） #

如果这个排序逻辑是高频查询，给计算列创建函数索引能让性能再上一个台阶：

-- 给排序用的计算列创建索引
CREATE INDEX idx_test_main_calc_col1 ON test_main ((col1 * 2));

之后再执行原始查询（或者优化后的查询），PostgreSQL会直接使用索引来排序，不需要全表扫描，直接取出前100行再计算其他列：

SELECT
  col1,
  (col1 * 2) AS calc_col1,
  sqrt(col1) AS calc_col2,
  col1 ^ 3 AS calc_col3,
  md5(col1::TEXT) AS calc_colN
FROM test_main
ORDER BY (col1 * 2)
LIMIT 100;

此时EXPLAIN ANALYZE会显示使用Index Scan using idx_test_main_calc_col1 on test_main，性能提升非常明显。

为什么原始查询会慢？ #

PostgreSQL的查询优化器默认会认为：一次性计算所有列的开销，比先取部分行再计算的开销更小。但当计算列复杂且数量多、数据量大时，这个假设就不成立了。我们通过手动拆分查询，强制优化器先处理排序取数，再做后续计算，就能避免不必要的资源浪费。

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