主持人 > 博客 > 管理 > PostgreSQL 反模式：有害的 JOIN 和 OR

PostgreSQL 反模式：有害的 JOIN 和 OR

小心帶來緩衝區的操作...
以一個小查詢為例，讓我們來看看一些在 PostgreSQL 中最佳化查詢的通用方法。 是否使用它們取決於您自己，但了解它們是值得的。

在 PG 的某些後續版本中，隨著調度程序變得更加智能，情況可能會發生變化，但對於 9.4/9.6，情況看起來大致相同，如這裡的範例所示。

讓我們提出一個非常實際的請求：

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

關於表名和欄位名欄位和表格的“俄語”名稱可以不同地對待，但這是一個品味問題。 因為 在張量這裡 沒有外國開發者，而 PostgreSQL 允許我們甚至用象形文字來命名，如果他們 用引號引起來，那麼我們更願意明確、清楚地命名對象，這樣就不會出現差異。
讓我們看看最終的計劃：
PostgreSQL 反模式：有害的 JOIN 和 OR
[看看 explain.tensor.ru]

144ms 和近 53K 緩衝區 ——也就是超過400MB的數據！ 如果在我們請求時所有這些都在快取中，我們將很幸運，否則從磁碟讀取時將花費數倍的時間。

演算法最重要！

為了以某種方式優化任何請求，您必須先了解它應該做什麼。
我們暫時將資料庫結構本身的開發放在本文的討論範圍之外，並同意我們可以相對“便宜” 重寫請求 和/或將一些我們需要的東西滾到底座上 指標.

所以請求：
— 檢查至少一些文件的存在
- 在我們需要的條件下並且是某種類型
- 作者或表演者是我們需要的員工

加入+限制1

通常，開發人員更容易編寫一個查詢，其中首先連接大量表，然後整個集合中僅保留一筆記錄。 但對於開發人員來說更容易並不代表資料庫更有效率。
在我們的例子中，只有 3 張桌子 - 效果如何...

我們先去掉與「文檔類型」表的連接，同時告訴資料庫： 我們的類型記錄是獨一無二的 （我們知道這一點，但調度程序還不知道）：

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

是的，如果表/CTE 由單一記錄的單一欄位組成，那麼在 PG 中你甚至可以這樣寫，而不是

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

PostgreSQL 查詢中的惰性求值

BitmapOr 與 UNION

在某些情況下，點陣圖堆掃描會讓我們付出很大的代價——例如，在我們的情況下，當相當多的記錄滿足所需條件時。 我們得到它是因為 OR 條件變成 BitmapOr- 按計畫運作。
讓我們回到最初的問題——我們需要找到一筆對應的記錄 任何人 從條件來看－即兩種條件下都不需要搜尋所有59K記錄。 有一種方法可以解決一個條件，並且 僅當第一個中沒有找到任何內容時才轉到第二個。 下面的設計將幫助我們：

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

「外部」LIMIT 1 確保在找到第一個記錄時結束搜尋。 如果已經在第一個區塊中找到，則不會執行第二個區塊（從未被執行過 方面）。

“CASE下隱藏困難條件”

原始查詢中有一個非常不方便的時刻 - 根據相關表格「DocumentExtension」檢查狀態。 無論表達式中其他條件的真實性如何（例如， d.「已刪除」不屬實），這個連線總是被執行並且「消耗資源」。 或多或少將被花費——取決於這張桌子的大小。
但是您可以修改查詢，以便僅在確實有必要時才搜尋相關記錄：

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

一旦從連結表給我們 結果不需要任何字段，那麼我們就有機會將 JOIN 轉換為子查詢的條件。
讓我們將索引欄位保留在“CASE 括號之外”，將記錄中的簡單條件添加到 WHEN 區塊 - 現在，僅在傳遞給 THEN 時才會執行“重”查詢。

我的姓氏是“總”

我們使用上述所有機制收集結果查詢：

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

調整索引

訓練有素的眼睛注意到 UNION 子區塊中的索引條件略有不同 - 這是因為我們在表上已經有了合適的索引。 如果它們不存在，那麼就值得創建： 文檔（Person3，文檔類型） и 文檔（文檔類型，員工）.
關於 ROW 條件中欄位的順序從策劃者的角度來看，當然可以這樣寫 (A, B) = (常數A, 常數B)和 (B, A) = (常數B, 常數A)。 但錄音時 依照索引中欄位的順序，這樣的請求只是為了以後調試更方便。
計劃中有什麼？
PostgreSQL 反模式：有害的 JOIN 和 OR
[看看 explain.tensor.ru]

不幸的是，我們運氣不好，第一個 UNION 區塊中什麼也沒找到，所以第二個 UNION 區塊仍然被執行。 但即便如此——也只是 0.037ms 和 11 個緩衝區!
我們加快了請求速度並減少了記憶體中的資料泵送 幾千次，使用相當簡單的技術 - 只需少量複製貼上即可獲得良好的結果。 🙂

來源： www.habr.com

添加評論