🥇PostgreSQL 反模式：讓我們用字典進行重型 JOIN

我們繼續致力於研究鮮為人知的方法來提高「看似簡單」的 PostgreSQL 查詢效能的系列文章：

別以為我那麼不喜歡JOIN...:)

但通常情況下，如果沒有它，請求的效率會比有它時高得多。所以今天我們會嘗試 擺脫資源密集型 JOIN - 使用字典。

從 PostgreSQL 12 開始，由於以下原因，下面描述的某些情況可能會略有不同：預設非物化 CTE。可以透過指定密鑰來恢復此行為 MATERIALIZED.

有限的詞彙中有很多“事實”

讓我們來看一個非常實際的應用程式任務 - 我們需要顯示一個列表收到的訊息或與寄件者的活動任務：

25.01 | Иванов И.И. | Подготовить описание нового алгоритма.
22.01 | Иванов И.И. | Написать статью на Хабр: жизнь без JOIN.
20.01 | Петров П.П. | Помочь оптимизировать запрос.
18.01 | Иванов И.И. | Написать статью на Хабр: JOIN с учетом распределения данных.
16.01 | Петров П.П. | Помочь оптимизировать запрос.

在抽象世界中，任務作者應該均勻分佈在我們組織的所有員工中，但實際上 通常，任務來自相當有限的人數 - 「來自管理階層」或來自鄰近部門（分析師、設計師、行銷等）的「分包商」。

讓我們接受這樣的事實：在我們 1000 人的組織中，只有 20 位作者（通常更少）為每個特定的執行者設定任務，並且讓我們利用這門學科知識加速“傳統”查詢。

腳本產生器

-- сотрудники
CREATE TABLE person AS
SELECT
  id
, repeat(chr(ascii('a') + (id % 26)), (id % 32) + 1) "name"
, '2000-01-01'::date - (random() * 1e4)::integer birth_date
FROM
  generate_series(1, 1000) id;

ALTER TABLE person ADD PRIMARY KEY(id);

-- задачи с указанным распределением
CREATE TABLE task AS
WITH aid AS (
  SELECT
    id
  , array_agg((random() * 999)::integer + 1) aids
  FROM
    generate_series(1, 1000) id
  , generate_series(1, 20)
  GROUP BY
    1
)
SELECT
  *
FROM
  (
    SELECT
      id
    , '2020-01-01'::date - (random() * 1e3)::integer task_date
    , (random() * 999)::integer + 1 owner_id
    FROM
      generate_series(1, 100000) id
  ) T
, LATERAL(
    SELECT
      aids[(random() * (array_length(aids, 1) - 1))::integer + 1] author_id
    FROM
      aid
    WHERE
      id = T.owner_id
    LIMIT 1
  ) a;

ALTER TABLE task ADD PRIMARY KEY(id);
CREATE INDEX ON task(owner_id, task_date);
CREATE INDEX ON task(author_id);

讓我們顯示特定執行器的最後 100 個任務：

SELECT
  task.*
, person.name
FROM
  task
LEFT JOIN
  person
    ON person.id = task.author_id
WHERE
  owner_id = 777
ORDER BY
  task_date DESC
LIMIT 100;

[看看 explain.tensor.ru]

事實證明， 1/3 總時間和 3/4 讀數 資料頁僅用於搜尋作者 100 次 - 每個輸出任務。但我們知道，在這數百人之中 只有20種不同 - 可以使用這些知識嗎？

hstore字典

讓我們充分利用儲存類型產生“字典”鍵值：

CREATE EXTENSION hstore

我們只需要把作者的 ID 和他的名字放入字典中，這樣我們就可以使用這個鍵來提取：

-- формируем целевую выборку
WITH T AS (
  SELECT
    *
  FROM
    task
  WHERE
    owner_id = 777
  ORDER BY
    task_date DESC
  LIMIT 100
)
-- формируем словарь для уникальных значений
, dict AS (
  SELECT
    hstore( -- hstore(keys::text[], values::text[])
      array_agg(id)::text[]
    , array_agg(name)::text[]
    )
  FROM
    person
  WHERE
    id = ANY(ARRAY(
      SELECT DISTINCT
        author_id
      FROM
        T
    ))
)
-- получаем связанные значения словаря
SELECT
  *
, (TABLE dict) -> author_id::text -- hstore -> key
FROM
  T;

[看看 explain.tensor.ru]

花費在獲取有關人員的資訊上 時間減少 2 倍，讀取的資料減少 7 倍！除了「詞彙」之外，還幫助我們取得這些成果的還有 大量記錄檢索 從表中單次使用 = ANY(ARRAY(...)).

表條目：序列化和反序列化

但是，如果我們不僅需要保存一個文字字段，而是需要保存字典中的整個條目怎麼辦？在這種情況下，PostgreSQL的能力就會幫助我們 將表條目視為單一值:

...
, dict AS (
  SELECT
    hstore(
      array_agg(id)::text[]
    , array_agg(p)::text[] -- магия #1
    )
  FROM
    person p
  WHERE
    ...
)
SELECT
  *
, (((TABLE dict) -> author_id::text)::person).* -- магия #2
FROM
  T;

讓我們看看這裡發生了什麼：

我們採取了 p 作為完整 person 表格條目的別名 並組裝了一系列。
這 錄音陣列被重新製作 到文字字串陣列 (person[]::text[]) 以將其作為值陣列放入 hstore 字典中。
當我們收到相關記錄時，我們 透過鍵從字典中提取 作為文字字串。
我們需要文字 轉成表類型值 person（為每個表自動建立一個同名的類型）。
使用以下命令將鍵入的記錄“擴展”為列 (...).*.

json字典

但是，如果沒有相應的表類型來進行“轉換”，我們上面應用的這種技巧將不起作用。將會出現完全相同的情況，如果我們嘗試使用 CTE 行，而非「真實」表.

在這種情況下他們會幫助我們用於處理 json 的函數:

...
, p AS ( -- это уже CTE
  SELECT
    *
  FROM
    person
  WHERE
    ...
)
, dict AS (
  SELECT
    json_object( -- теперь это уже json
      array_agg(id)::text[]
    , array_agg(row_to_json(p))::text[] -- и внутри json для каждой строки
    )
  FROM
    p
)
SELECT
  *
FROM
  T
, LATERAL(
    SELECT
      *
    FROM
      json_to_record(
        ((TABLE dict) ->> author_id::text)::json -- извлекли из словаря как json
      ) AS j(name text, birth_date date) -- заполнили нужную нам структуру
  ) j;

需要注意的是，在描述目標結構時，我們不能列出來源字串的所有字段，而只能列出我們真正需要的字段。如果我們有一個「本機」表，那麼最好使用該函數 json_populate_record.

我們仍然訪問字典一次，但是 json-[de]序列化成本相當高因此，只有在「誠實的」CTE 掃描表現較差的情況下才使用此方法是合理的。

測試性能

因此，我們有兩種方法將資料序列化到字典中 - hstore/json_object。此外，鍵和值數組本身也可以透過兩種方式生成，內部或外部轉換為文字： array_agg(i::text) / array_agg(i)::text[].

讓我們使用一個純粹的合成範例來檢查不同類型序列化的有效性 - 序列化不同數量的密鑰:

WITH dict AS (
  SELECT
    hstore(
      array_agg(i::text)
    , array_agg(i::text)
    )
  FROM
    generate_series(1, ...) i
)
TABLE dict;

評估腳本：序列化

WITH T AS (
  SELECT
    *
  , (
      SELECT
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          SELECT
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                SELECT
                  hstore(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              TABLE dict
            $$) T(el text)
        ) T
    ) et
  FROM
    generate_series(0, 19) v
  ,   LATERAL generate_series(1, 7) i
  ORDER BY
    1, 2
)
SELECT
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

在 PostgreSQL 11 上，字典大小最多約 2^12 個鍵 序列化為 json 需要更少的時間。在這種情況下，最有效的就是 json_object 和「內部」類型轉換的結合 array_agg(i::text).

現在讓我們嘗試讀取每個鍵的值 8 次 - 畢竟，如果您不訪問字典，那麼為什麼需要它呢？

評估腳本：閱讀字典

WITH T AS (
  SELECT
    *
  , (
      SELECT
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          SELECT
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                SELECT
                  json_object(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              SELECT
                (TABLE dict) -> (i % ($$ || (1 << v) || $$) + 1)::text
              FROM
                generate_series(1, $$ || (1 << (v + 3)) || $$) i
            $$) T(el text)
        ) T
    ) et
  FROM
    generate_series(0, 19) v
  , LATERAL generate_series(1, 7) i
  ORDER BY
    1, 2
)
SELECT
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

並且...已經大約 使用 2^6 個鍵，從 json 字典讀取開始多次遺失 從 hstore 讀取，對於 jsonb，同樣的情況發生在 2^9 處。

最終結論：

如果你需要這樣做 JOIN 多個重複記錄 — 最好使用表格的“字典”

如果你的字典是預期的 小，你不會從中讀到太多內容 - 你可以使用 json[b]

在所有其他情況下 hstore + array_agg(i::text) 會更有效

來源： www.habr.com

PostgreSQL 反模式：字典 Hit Heavy JOIN

有限的詞彙中有很多“事實”

hstore字典

表條目：序列化和反序列化

json字典

測試性能

添加評論取消回复

PostgreSQL 反模式：字典 Hit Heavy JOIN

有限的詞彙中有很多“事實”

hstore字典

表條目：序列化和反序列化

json字典

測試性能

添加評論 取消回复

添加評論取消回复