主持人 > 博客 > 管理 > PostgreSQL 反模式：CTE x CTE

PostgreSQL 反模式：CTE x CTE

由於我的工作原因，我必須處理開發人員編寫請求並認為“底座很聰明，它可以自己處理一切！«

在某些情況下（部分是由於對資料庫功能的無知，部分是由於過早的優化），這種方法會導致「科學怪人」的出現。

首先，我舉一個這樣的請求的例子：

-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

為了實質地評估請求的質量，讓我們建立一些任意資料集：

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

事實證明 讀取資料花了不到四分之一的時間 查詢執行：

PostgreSQL 反模式：CTE x CTE[看看 explain.tensor.ru]

把它一塊一塊拆開

我們仔細看看請求，不解：

  1. 如果沒有遞迴 CTE，為什麼這裡會出現「WITH RECURSIVE」？
  2. 如果最小/最大值無論如何都與原始樣本相關聯，為什麼還要將它們分組到單獨的 CTE 中？
    +25% 時間
  3. 為什麼在最後使用無條件「SELECT * FROM」來重複先前的 CTE？
    +14% 時間

在這種情況下，我們很幸運地選擇了 Hash Join，而不是 Nested Loop 來進行連接，因為那樣我們將不僅收到一個 CTE Scan pass，而是收到 10K 個！

關於 CTE 掃描的一些信息這裡我們必須記住一點 CTE 掃描與 Seq 掃描類似 - 也就是說，沒有索引，而只是完整的搜索，這需要 10K x 0.3 毫秒 = 3000ms 對於 cte_max 的週期 或 1K x 1.5 毫秒 = 1500ms 當透過 cte_bind 循環時!
其實你想得到什麼結果呢？ 是的，通常這是在分析「三層」查詢的第五分鐘時出現的問題。

我們想要輸出每個唯一的密鑰對 key_a 分組的最小值/最大值.
所以讓我們用它來做這個 視窗函數:

SELECT DISTINCT ON(key_a, key_b)
	key_a a
,	key_b b
,	max(fld1) OVER(w) bind_fld1
,	min(fld2) OVER(w) bind_fld2
FROM
	tbl
WINDOW
	w AS (PARTITION BY key_a);

PostgreSQL 反模式：CTE x CTE
[看看 explain.tensor.ru]

由於讀取兩個選項中的資料大約需要 4-5 毫秒，因此我們所有的時間都增加了 -32％ - 這是最純粹的形式 從基礎 CPU 移除負載，如果這樣的請求執行得夠頻繁。

一般來說，你不應該強迫底座「承載圓的，滾動方的」。

來源： www.habr.com

添加評論