PostgreSQL中ORDER BY LIMIT为何更高效？前两种SQL如何优化？

场景与表结构 #

需查询数据库中每个machine_id对应的最新receive_time数据，其中machine_data表约有1.3亿条记录。

machine_data表结构 #

create table public.machine_data( 
    machine_id integer not null 
    ,receive_time timestamp (6) with time zone not null 
    ,code integer not null 
    ,primary key (machine_id, receive_time)
);

machine_info表结构 #

create table public.machine_info (
  machine_id serial not null
  , model integer not null
  , primary key (machine_id)
);

尝试的SQL与问题 #

1. 最初使用的SQL（Java服务查询超时）：

WITH LatestMachineData AS ( 
    SELECT
        machine_id
        , receive_time
        , code
        , ROW_NUMBER() OVER (PARTITION BY machine_id ORDER BY receive_time DESC) as rn 
    FROM
        machine_data
) 
SELECT
    * 
FROM
    LatestMachineData 
WHERE
    rn = 1;

2. AI生成的SQL（服务仍超时）：

SELECT
    md.* 
FROM
    machine_data md 
    inner join machine_info mi 
        on mi.machine_id = md.machine_id 
    INNER JOIN ( 
        SELECT
            machine_id
            , MAX(receive_time) AS latest_receive_time 
        FROM
            machine_data 
        GROUP BY
            machine_id
    ) latest_data 
        ON mi.machine_id = latest_data.machine_id 
        AND md.receive_time = latest_data.latest_receive_time;

3. 最终高效运行的SQL：

select
    * 
from
    machine_data md 
where
    (md.machine_id,md.receive_time) in ( 
        SELECT
            a.machine_id
            , a.receive_time as receive_time 
        FROM
            ( 
                SELECT
                    mi.machine_id
                    , ( 
                        SELECT
                            receive_time 
                        FROM
                            machine_data md 
                        WHERE
                            mi.machine_id = md.machine_id 
                        ORDER BY
                            receive_time DESC 
                        LIMIT
                            1
                    ) as receive_time 
                FROM
                    machine_info as mi 
            ) a 
        where
            a.receive_time is not null
    );

疑问 #

• 为何ORDER BY + LIMIT的执行速度远快于其他两种方式？
• 能否对前两种SQL语句进行优化？

一、ORDER BY + LIMIT高效的原因 #

结合表结构的主键(machine_id, receive_time)，这个复合主键本身是按machine_id分组、receive_time升序存储的有序结构，核心优势在于：

1. 精准单条数据定位：针对每个machine_id，子查询会直接利用主键索引，反向遍历即可快速拿到该分组下的最大receive_time记录，无需扫描该machine_id的所有数据，也不需要全局排序或分组聚合。
2. 驱动表范围缩小：以machine_info为驱动表，仅处理实际存在的machine_id，避免了对machine_data全表的扫描或聚合操作——前两种方案均以machine_data为核心进行全表处理，1.3亿条数据的全表计算会带来极大的资源开销。
3. 轻量化执行计划：嵌套子查询的逻辑简单，数据库优化器可直接针对每个machine_id执行一次索引查找，无需构建临时表存储窗口函数结果或分组聚合结果，内存与IO开销远低于前两种方案。

二、前两种SQL的优化方案 #

1. 窗口函数版本的优化 #

原方案需对machine_data全表计算窗口函数，开销极大，可通过缩小处理范围+利用索引优化：

WITH LatestMachineData AS ( 
    SELECT
        md.machine_id
        , md.receive_time
        , md.code
        , ROW_NUMBER() OVER (PARTITION BY md.machine_id ORDER BY md.receive_time DESC) as rn 
    FROM
        machine_info mi
        JOIN machine_data md ON mi.machine_id = md.machine_id
) 
SELECT
    machine_id, receive_time, code
FROM
    LatestMachineData 
WHERE
    rn = 1;

优化点：

• 以machine_info为驱动表，仅处理存在的machine_id，过滤无效数据。
• 复用主键索引的有序性，窗口函数的排序操作无需额外计算。

另外，可改用PostgreSQL专属的DISTINCT ON语法，这是获取每组第一条数据的高效方式：

SELECT DISTINCT ON (md.machine_id)
    md.*
FROM
    machine_info mi
    JOIN machine_data md ON mi.machine_id = md.machine_id
ORDER BY
    md.machine_id, md.receive_time DESC;

DISTINCT ON会直接利用主键索引，按machine_id分组后取每组第一条数据，性能远优于通用窗口函数方案。

2. 分组聚合版本的优化 #

原方案先全表分组聚合再关联，开销大，可调整关联顺序+利用索引优化：

SELECT
    md.*
FROM
    machine_info mi
    JOIN (
        SELECT
            machine_id,
            MAX(receive_time) AS latest_receive_time
        FROM
            machine_data
        GROUP BY
            machine_id
    ) latest_data ON mi.machine_id = latest_data.machine_id
    JOIN machine_data md ON md.machine_id = latest_data.machine_id 
        AND md.receive_time = latest_data.latest_receive_time;

优化点：

• 先通过machine_info过滤分组结果，减少后续关联的数据量（若machine_info数据量远小于machine_data分组数）。
• 主键索引可直接支持GROUP BY machine_id与MAX(receive_time)计算，无需全表扫描。

更简洁的优化方式是使用PostgreSQL的LATERAL关联，逻辑与高效SQL一致：

SELECT
    md.*
FROM
    machine_info mi
    LATERAL (
        SELECT *
        FROM machine_data md
        WHERE md.machine_id = mi.machine_id
        ORDER BY md.receive_time DESC
        LIMIT 1
    ) md;

该写法会逐个处理machine_info中的machine_id，利用主键索引快速获取最新记录，执行效率与你最终使用的SQL相当。

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