sql - 哪一个更快/优化 - 内部连接或分区 - 以获得聚合数据？

Question

在我的数据“table1”中，每个 app_id 都有多条记录。我正在尝试获取最新的 app_id 条目。就性能和内存使用而言，考虑以下哪一项查询会更快或更好？目前，我在 AWS 服务器上工作并使用 PostgreSQL。

查询 1:

SELECT b.* 
FROM 
    (SELECT app_id, max(datetime) as datetime
    from table1
    group by 1) a
INNER JOIN 
    (SELECT * 
    from table1) b
ON a.app_id = b.app_id AND a.datetime = b.datetime

查询 2:

SELECT * 
FROM 
    (SELECT *, row_number() over (partition by app_id order by datetime DESC 
    NULLS LAST) as num FROM table1) sub
WHERE sub.num=1

Answer 1

“哪个更快”的正确答案是尝试对您的数据和系统进行查询。

也就是说，有一些支持 row_number() 的考虑因素。特别是，窗口函数不是数据库中的“偶然”特性。添加新的字符串函数只是一个函数，该函数可能会优化也可能不会优化。

另一方面，窗口函数需要重写/重新设计数据库引擎的一些基本组件。一般来说，这是在考虑性能的情况下完成的。因此，我通常发现窗口函数比等效构造更快。

具有讽刺意味的是，我经常发现(跨数据库)的唯一异常(exception)适用于您的情况。而且，它没有使用 join 和 group by。相反，它是:

select t1.*
from table1 t1
where t1.datetime = (select max(tt1.datetime)
                     from table1 tt1
                     where tt1.app_id = t1.app_id
                    );

连同 table1(app_id, datetime) 上的索引。

性能改进的基本原因是它扫描一次 table1 并在每一行进行索引查找。

join/group by 会多次扫描表，聚合开销很大。 row_number() 版本扫描表(或索引)，计算值，然后将值带回每一行——类似于数据的两次扫描。