按id排序SQL查询缓慢的原因及相关技术疑问

orders和results表各有7500行数据，以下查询执行极慢（耗时10秒）：

select 
"orders"."id", "results"."order_id", COUNT(*) OVER () as "count" 
from "orders" left join results on "orders"."id" = "results"."order_id" 
order by "orders"."id" desc limit 5;

疑问点 #

1. 将排序字段改为无索引的created_at，查询速度极快，但id是主键且有索引，按id排序理应更快，原因是什么？
2. 仍按id排序，但将limit值提高至50，查询速度也变快，原因是什么？
3. 按id排序、limit为5，但移除COUNT(*) OVER()函数，查询速度同样变快，原因是什么？

业务限制：无法改用created_at排序，必须限制返回5条数据且保留COUNT(*) OVER()函数。

补充表结构：

create table orders (
    id bigint generated by default as identity,
    created_at timestamp with time zone null default now(),
    ...-- 其他文本字段

    constraint orders_pkey primary key (id)
);

create table results (
    id bigint generated by default as identity,
    order_id bigint null,
    ...-- 其他文本字段

    constraint results_pkey primary key (id),
    constraint results_order_id_orders_id_fk foreign key (order_id) references orders (id) on delete cascade,
);

疑问解答 #

1. 按无索引的created_at排序更快的原因 #

当按created_at排序时，PostgreSQL优化器判断无法通过索引快速获取排序结果，于是选择先全表扫描orders并排序，再通过嵌套循环逐行关联results——因为limit 5，只需要处理排序后的前5条orders，对应的results关联也仅针对这5个order_id。而COUNT(*) OVER()的总行数计算，优化器会通过单独的快速统计（比如利用系统元数据或轻量聚合）完成，不需要处理全量关联结果。

但按主键id排序时，优化器误判了成本，选择先做全表左连接（把所有7500条orders和对应的results全部关联，生成远大于7500行的结果集），再对这个庞大的结果集排序后取前5条，全量关联+排序的操作耗时极高。

2. limit提高到50时速度变快的原因 #

当limit从5提升到50，优化器的成本估算发生变化：它认为**通过主键索引获取前50条orders（按id降序），再嵌套循环关联results**的成本，远低于先全量join再排序取前50的成本，于是切换了执行计划。此时只需要处理50条orders及其对应的results，避免了全量关联和排序，速度自然变快。

3. 移除COUNT(*) OVER()后速度变快的原因 #

移除COUNT(*) OVER()后，查询只需要返回前5条数据。优化器可以直接通过orders的主键索引获取最后5条id，再嵌套循环关联results，完全不需要统计全量关联结果的总行数——彻底避开了全量join和排序的低效操作，所以速度骤升。

而保留COUNT(*) OVER()时，优化器错误认为必须先完成全量关联才能得到总行数，因此选择了低效的全量join+排序路径。

满足业务需求的优化方案 #

方案1：拆分查询，分离总行数统计与数据查询 #

WITH total_count AS (
    SELECT COUNT(*) AS total
    FROM orders
    LEFT JOIN results ON orders.id = results.order_id
)
SELECT 
    o.id, r.order_id, tc.total AS count
FROM orders o
LEFT JOIN results r ON o.id = r.order_id
ORDER BY o.id DESC
LIMIT 5
CROSS JOIN total_count tc;

该方案让优化器先单独统计总行数（利用系统统计信息快速完成），再通过主键索引获取前5条orders并关联results，彻底避免全量join后排序的操作。

方案2：给results表的order_id加索引 #

创建索引加速关联操作：

CREATE INDEX idx_results_order_id ON results(order_id);

配合方案1使用，能进一步降低嵌套循环关联results的耗时。

方案3：临时强制优化器选择高效路径 #

临时关闭哈希连接（仅当前会话生效），让优化器不得不选择嵌套循环路径：

SET enable_hashjoin = off;
select 
"orders"."id", "results"."order_id", COUNT(*) OVER () as "count" 
from "orders" left join results on "orders"."id" = "results"."order_id" 
order by "orders"."id" desc limit 5;
SET enable_hashjoin = on;

注意不要全局关闭哈希连接，仅在执行该查询时临时使用。

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