我们如何优化 Postgres 中的分组最大查询以避免过多的表扫描？

优化分组最大查询

相关查询旨在检索记录表中每个唯一 option_id 具有最大 id 值的行。然而，由于过多的表扫描，当前的实现效率很低。

为什么当前查询效率低下

问题在于用于识别行的嵌套循环连接最大 id 值。此连接需要 Postgres 多次扫描整个记录表，从而导致较高的执行时间和资源消耗。

使用查找表的替代方法

优化此查询，建议使用替代方法：创建一个名为 options 的单独查找表，将选项 ID 映射到记录表中的最大 ID。在records.option_id和options.option_id之间引入外键约束将确保引用完整性。

CREATE TABLE options (
  option_id int PRIMARY KEY,
  option text UNIQUE NOT NULL
);

INSERT INTO options (option_id, option)
SELECT DISTINCT option_id, 'option' || option_id
FROM records;

使用相关子查询优化查询

使用选项表，可以使用相关子查询重写原始查询，该子查询根据 option_id 有效地连接两个表

SELECT o.option_id, (SELECT MAX(id) FROM records WHERE option_id = o.option_id) AS max_id
FROM options o
ORDER BY o.option_id;

替代方法的优点

这种替代方法有几个优点：

• 通过仅访问来减少表扫描选项表中的相关行。
• 利用高效的相关子查询来检索记录中的最大 id 值。
• 通过外键约束保持引用完整性。

其他优化

向Records table on (option_id, id DESC NULLS LAST) 可以通过允许 Postgres 执行仅索引来进一步增强性能扫描子查询。

以上是我们如何优化 Postgres 中的分组最大查询以避免过多的表扫描？的详细内容。更多信息请关注PHP中文网其他相关文章！