首頁 >資料庫 >mysql教程 >為什麼我的 PostgreSQL 具有複合主鍵的 SELECT DISTINCT 查詢如此緩慢?

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

Linda Hamilton
Linda Hamilton原創
2025-01-07 18:23:40587瀏覽

Why is My PostgreSQL SELECT DISTINCT Query with a Composite Primary Key So Slow?

PostgreSQL SELECT DISTINCT 複合鍵的效能問題

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

為什麼這麼慢?

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

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

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

<code class="language-sql">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;</code>

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

這種方法的優點

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

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

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn