🥇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-dictionary

使ってみよう hstore タイプ「辞書」の Key-Value を生成するには:

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、データ読み取り時間は XNUMX 分の XNUMX に短縮！この成果を達成するのに貢献したのは「語彙力」に加えて、 レコードの一括取得 を使用してシングルパスでテーブルから = ANY(ARRAY(...)).

テーブルエントリ: シリアル化と逆シリアル化

しかし、XNUMX つのテキストフィールドだけでなく、辞書のエントリ全体を保存する必要がある場合はどうすればよいでしょうか? この場合、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 スキャンの結果が悪くなる場合にのみ使用するのが合理的です。

テストパフォーマンス

したがって、データを辞書にシリアル化するXNUMXつの方法があります- hstore/json_object。さらに、キーと値の配列自体も、テキストへの内部変換または外部変換を使用して XNUMX つの方法で生成できます。 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) より効果的になります

出所： habr.com

PostgreSQL のアンチパターン: ディクショナリを使用して重い JOIN を実行しましょう

限られた語彙の中にたくさんの「事実」が詰まっている

hstore-dictionary

テーブルエントリ: シリアル化と逆シリアル化

json辞書

テストパフォーマンス

コメントを追加します返信をキャンセル

PostgreSQL のアンチパターン: ディクショナリを使用して重い JOIN を実行しましょう

限られた語彙の中にたくさんの「事実」が詰まっている

hstore-dictionary

テーブルエントリ: シリアル化と逆シリアル化

json辞書

テストパフォーマンス

コメントを追加します 返信をキャンセル

コメントを追加します返信をキャンセル