我們如何優化 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中文網其他相關文章！