為什麼我的 PostgreSQL 具有複合主鍵的 SELECT DISTINCT 查詢如此緩慢？

PostgreSQL SELECT DISTINCT 複合鍵的效能問題

在具有複合主鍵（例如 SELECT DISTINCT）的 PostgreSQL 表上使用 (product_id, trade_id) 可能會非常慢。 查詢規劃器經常選擇順序掃描，而不是有效地利用索引。

為什麼這麼慢？

• 順序掃描偏好：規劃者可能更喜歡全表掃描，即使有索引，也會導致查詢時間顯著延長。
• 缺少索引跳過掃描：PostgreSQL 本身不支援索引跳過掃描，這是一種透過跳過索引中的重複項來有效檢索唯一值的最佳化。

解決方案：使用 CTE 模擬索引跳躍掃描

雖然真正的索引跳過掃描不可用，但我們可以使用遞歸公共表表達式 (CTE) 有效地模仿其行為：

WITH RECURSIVE cte AS (
   (   -- parentheses are crucial
   SELECT product_id
   FROM   tickers
   ORDER  BY 1
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT product_id
      FROM   tickers t
      WHERE  t.product_id > c.product_id
      ORDER  BY 1
      LIMIT  1
      ) l
   )
SELECT * FROM cte;

此 CTE 依排序順序迭代唯一的 product_id 值，利用 (product_id) 上的索引來提高效率。

這種方法的優點

• 速度改善：此方法大幅減少了查詢執行時間。 測試顯示，在 225 萬行表上，時間縮短至 0.75 毫秒。
• 索引利用率：它有效地利用了複合主鍵索引(product_id, trade_id)和(product_id)上的索引。
• 資料分佈不可知：無論表內的資料分佈為何，效能都保持一致。

以上是為什麼我的 PostgreSQL 具有複合主鍵的 SELECT DISTINCT 查詢如此緩慢？的詳細內容。更多資訊請關注PHP中文網其他相關文章！