為什麼 PostgreSQL 中複合主鍵的表上 SELECT DISTINCT 很慢，如何最佳化？

PostgreSQL 中使用複合主鍵的表上 SELECT DISTINCT 查詢速度慢的原因及最佳化方法

在 PostgreSQL 資料庫中，SELECT DISTINCT 查詢的執行速度取決於表格結構和資料分佈。雖然 tickers 表中的 product_id 欄位是複合主鍵的一部分，因此在其上建立了索引，但使用 SELECT DISTINCT product_id FROM tickers 取得唯一 product_id 的查詢預設會執行順序掃描。

表現慢的原因

表現慢的主要原因是表中存在 product_id 的重複值。這意味著對於每個檢索到的唯一 product_id，PostgreSQL 必須掃描整個表以確保不存在重複項。

解決方法：模擬索引跳躍掃描

由於 PostgreSQL 尚未原生支援索引跳躍掃描，因此可以使用遞歸 CTE (公用表表達式) 來模擬此行為。此 CTE 迭代地檢索並丟棄重複項，有效地過濾掉除每個唯一 product_id 的一個實例之外的所有實例。

改良後的解決方案

<code class="language-sql">WITH RECURSIVE cte AS (
   (   -- 括号必需
   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
   )
TABLE  cte;</code>

此查詢使用橫向連接來遍歷排序後的表，並使用 orderBy 檢索唯一的 product_id 值。

結論

透過使用 CTE 方法模擬索引跳躍掃描，可以顯著提高 SELECT DISTINCT product_id 查詢的執行時間，從而減少從 tickers 表檢索唯一 product_id 所需的時間。

以上是為什麼 PostgreSQL 中複合主鍵的表上 SELECT DISTINCT 很慢，如何最佳化？的詳細內容。更多資訊請關注PHP中文網其他相關文章！