PostgreSQL 未在 FULL OUTER JOIN 中使用索引

我有這些表我想做一個FULL OUTER JOIN。它們都有一個索引(ts_minute, user_id, campaign_id, host_id)（與連接條件匹配）。在以下查詢中，如果我FULL OUTER從查詢中刪除，它的執行速度會快 10,000 倍，因為它使用索引（使用 確認EXPLAIN SELECT ...）。問題是，我需要FULL OUTER.

select user_id, campaign_id, sum(i.cost), sum(b.bids_internal), sum(b.eligible_requests)
   from impr_summary_test i
   join bid_summary_test b USING (ts_minute, user_id, campaign_id, host_id)
   where ts_minute > now() - interval '5 min'
   group by 1, 2 order by 1,2;

也有索引(ts_minute)。如果能使用這個索引就好了。慢速情況下返回的行數約為 330，快速情況下返回的行數約為 220。

Postgres 9.3。

我已經嘗試將其重寫為兩個單獨的子查詢，每個子查詢都有自己的（相同的）WHERE子句，然後JOIN‘ing 那些結果，這確實加快了速度，但基本INNER JOIN情況仍然比 re- 快 5 倍寫。

如何通過讓它使用索引來加快速度？

bid_summary_test 中的行數：8,292,673

bid_summary_test 中匹配謂詞的行數ts_minute > now() - interval '5 min'：28,849

impr_summary_test 中的行數：2,523,534

impr_summary_test 中匹配謂詞的行數ts_minute > now() - interval '5 min'：11,081

當組合兩個表時，FULL OUTER JOIN 所有 行都包含在結果中。通常，對連接列使用索引是沒有意義的。這就是為什麼嘗試打開索引(ts_minute, user_id, campaign_id, host_id)將毫無結果的原因。恰好第一列也適合相關謂詞 on ts_minute。見下文。

該查詢還返回顯著更少的行，[INNER] JOIN這解釋了一些性能差異 - 但肯定不是因素 10.000。您更新的資訊確認返回的行數較少（220 對 330），但這還不是全部。在聚合之前，差異可能會大得多。

您的謂詞ts_minute是索引支持的更好候選者。有兩個索引似乎適用於謂詞ts_minute > now() - interval '5 min'（關於實際數據類型的未決資訊）。但是外觀是騙人的：在ts_minute 之後的結果列FULL [OUTER] JOIN是兩個基礎表中的列的組合。它就像一個表達式，它是**不可分割**的，並且不能ts_minute在基礎表上使用索引。

您必須在允許索引支持之前應用謂詞。FULL [OUTER] JOIN這就是您在添加的測試中已經通過兩個單獨的子查詢偶然發現的內容。EXPLAIN如果您檢查輸出，您會發現（僅）索引掃描或點陣圖索引掃描。

由於 you GROUP BY user_id, campaign_id，是否在此查詢中添加列和連接條件是**無關緊要的。**僅對條件是必需的。ts_minute``host_id``ts_minute``WHERE

SELECT user_id, campaign_id
    , i.sum_cost, b.sum_bids_internal, b.sum_eligible_requests
FROM  (
  SELECT user_id, campaign_id
       , sum(cost) AS sum_cost
  FROM   impr_summary_test
  WHERE  ts_minute > now() - interval '5 min'
  GROUP  BY 1, 2
  ) i 
FULL JOIN (
  SELECT user_id, campaign_id
       , sum(bids_internal) AS sum_bids_internal
       , sum(eligible_requests) AS sum_eligible_requests
  FROM   bid_summary_test
  WHERE  ts_minute > now() - interval '5 min'
  GROUP  BY 1, 2
  ) b USING (user_id, campaign_id)
ORDER  BY 1,2;

假設每個表中只有很小百分比的行位於“最後 5 分鐘”內，那麼ts_minute兩個表中的索引應該會非常快。

如果您的寫入模式並autovacuum允許僅索引掃描，則這些索引將為您提供最佳性能：

CREATE INDEX ON impr_summary_test (ts_minute, user_id, campaign_id, cost);
CREATE INDEX ON bid_summary_test  (ts_minute, user_id, campaign_id, bids_internal, eligible_requests);

權衡高度專業化指標的成本和收益。

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