與使用聚合的子查詢相關的慢查詢

這是我正在執行的查詢的簡化版本：

select
   ...
from
   editions
   join edition_events on edition_events.edition_id = editions.id
   join events on events.id = edition_events.event_id
   join (
       select
           event_id,
           array_agg(date_start),
           array_agg(date_end)
       from
           event_dates
       group by
           event_id
       ) as dates on dates.event_id = events.id
WHERE
   editions.id = ?;

這些查詢在我的測試數據（約 1500 個事件）中執行良好。現在我開始使用真實數據的副本（~400,000 個事件，~500,000 個 event_dates），我的應用程序在我從數據庫返回結果之前超時。我已經在所有相關列上添加了索引，這只是有點幫助。我已將問題縮小到使用聚合的子查詢。

現在，這些子查詢的真實版本執行時區操作或計數而不是 array_agg，因此在許多情況下使用觸發器更新計數器列是不切實際的。與事件關聯的日期必須是單獨的行，而不是儲存為數組，因為它們與其他表具有外鍵關係。

我在這裡提高性能的最佳選擇是什麼？我知道首先要評估子查詢，但是在完成聯接之前，我不知道實際上需要查看哪些日期。我看過橫向連接，但我不確定它們是否會在這裡有所幫助（而且我能找到的大多數資訊都類似於“看，新功能！”）。由於我仍在使用 9.2，如果它不能真正解決我的問題，我不想要求升級到 9.3+。

當您需要檢索整個表或其中的大部分，或者如果您可以在子查詢中廉價地限制相關行 - 或者當聚合表很小時，您的方法（“先聚合，後加入” ）通常表現最佳。看：

• 使用 LEFT JOIN 查詢不返回計數為 0 的行

對於結果中的幾行，這對於大表來說變得不成比例地昂貴，因為必須處理整個表（event_dates在您的情況下）。在這些情況下，最好將範式恢復為**“先加入，後聚合”**。等效的查詢將是：

SELECT e.id
    , array_agg(ed.date_start) AS starts
    , array_agg(ed.date_end)   AS ends
    , ...
FROM   editions       e
JOIN   edition_events ee ON ee.edition_id = e.id
JOIN   events         ev ON ev.id = ee.event_id
JOIN   event_dates    ed ON ed.event_id = e.id
WHERE  e.id = ?
GROUP  BY e.id;

這應該有助於任何版本的 Postgres。

還使用表別名來簡化語法。

我看不出LATERAL（Postgres 9.3+）如何幫助這個特定的查詢。您將使用它來引用連接樹左側的列，否則您無法在同一查詢級別上訪問該列，通常會生成要連接的多行。這裡也不是這樣。你所想的可以用普通的舊相關子查詢來實現，但那些通常表現更差。

不要相信我的話。對您的案例（使用您的真實數據）進行快速測試EXPLAIN ANALYZE，然後您就知道了。

引用自：https://dba.stackexchange.com/questions/54719