ProHoster > Blog > administracja > Antywzorce PostgreSQL: Słownik Hit Heavy JOIN

Antywzorce PostgreSQL: Słownik Hit Heavy JOIN

Kontynuujemy cykl artykułów poświęconych badaniu mało znanych sposobów na poprawę wydajności „pozornie prostych” zapytań PostgreSQL:

Nie myślcie, że tak bardzo nie lubię DOŁĄCZYĆ... :)

Ale często bez niego prośba okazuje się znacznie bardziej produktywna niż z nią. Więc dzisiaj spróbujemy pozbądź się wymagającego dużych zasobów JOIN - korzystanie ze słownika.

Antywzorce PostgreSQL: Słownik Hit Heavy JOIN

Począwszy od PostgreSQL 12, niektóre z opisanych poniżej sytuacji mogą być odtwarzane nieco inaczej ze względu na domyślny CTE niematerializacji. To zachowanie można odwrócić, określając klucz MATERIALIZED.

Mnóstwo „faktów” w ograniczonym słownictwie

Weźmy bardzo realne zadanie aplikacji - musimy wyświetlić listę wiadomości przychodzące lub aktywne zadania z nadawcami:

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

W abstrakcyjnym świecie autorzy zadań powinni być równomiernie rozdzieleni pomiędzy wszystkich pracowników naszej organizacji, ale w rzeczywistości zadania pochodzą z reguły od dość ograniczonej liczby osób - „od kierownictwa” w górę hierarchii lub „od podwykonawców” z sąsiednich działów (analitycy, projektanci, marketing, ...).

Przyjmijmy, że w naszej 1000-osobowej organizacji tylko 20 autorów (zwykle nawet mniej) wyznacza zadania dla każdego konkretnego wykonawcy i Wykorzystajmy tę wiedzę przedmiotowąaby przyspieszyć „tradycyjne” zapytanie.

Generator skryptów

-- сотрудники
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);

Pokażmy ostatnie 100 zadań dla konkretnego wykonawcy:

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;

Antywzorce PostgreSQL: Słownik Hit Heavy JOIN
[patrz na explain.tensor.ru]

Okazuje się, że 1/3 całkowitego czasu i 3/4 odczytów strony danych zostały utworzone wyłącznie w celu wyszukania autora 100 razy - dla każdego zadania wyjściowego. Ale my to wiemy wśród tych setek tylko 20 różnych - Czy można tę wiedzę wykorzystać?

słownik hstore

Użyjmy typ sklepu aby wygenerować parę klucz-wartość „słownik”:

CREATE EXTENSION hstore

Musimy tylko umieścić identyfikator autora i jego nazwisko w słowniku, abyśmy mogli następnie wyodrębnić za pomocą tego klucza:

-- формируем целевую выборку
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;

Antywzorce PostgreSQL: Słownik Hit Heavy JOIN
[patrz na explain.tensor.ru]

Wydane na uzyskanie informacji o osobach 2 razy mniej czasu i 7 razy mniej odczytanych danych! Oprócz „słownictwa” w osiągnięciu tych wyników pomogło nam także: masowe pobieranie rekordów ze stołu w jednym przejściu za pomocą = ANY(ARRAY(...)).

Wpisy w tabeli: serializacja i deserializacja

Co jednak, jeśli będziemy musieli zapisać w słowniku nie tylko jedno pole tekstowe, ale cały wpis? W tym wypadku z pomocą przyjdzie nam umiejętność PostgreSQL traktuj wpis tabeli jako pojedynczą wartość:

...
, 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;

Przyjrzyjmy się, co się tutaj działo:

  1. Wzięliśmy p jako alias do wpisu pełnej tabeli osób i zebrał ich szereg.
  2. to przekształcono szereg nagrań do tablicy ciągów tekstowych (person[]::text[]), aby umieścić ją w słowniku hstore jako tablicę wartości.
  3. Kiedy otrzymamy powiązany zapis, my wyciągnięte ze słownika według klucza jako ciąg tekstowy.
  4. Potrzebujemy tekstu zamień na wartość typu tabeli osoba (dla każdej tabeli automatycznie tworzony jest typ o tej samej nazwie).
  5. „Rozwiń” wpisany rekord do kolumn za pomocą (...).*.

słownik jsona

Ale taka sztuczka, którą zastosowaliśmy powyżej, nie zadziała, jeśli nie ma odpowiedniego typu tabeli do wykonania „rzucania”. Dokładnie taka sama sytuacja powstanie, jeśli spróbujemy skorzystać wiersz CTE, a nie „prawdziwa” tabela.

W tym przypadku nam pomogą funkcje do pracy z jsonem:

...
, 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;

Należy zaznaczyć, że opisując docelową strukturę nie możemy wyszczególnić wszystkich pól ciągu źródłowego, a jedynie te, które są nam naprawdę potrzebne. Jeśli mamy tabelę „natywną”, lepiej skorzystać z tej funkcji json_populate_record.

Nadal mamy dostęp do słownika raz, ale json-[de]koszty serializacji są dość wysokiedlatego rozsądne jest stosowanie tej metody tylko w niektórych przypadkach, gdy „uczciwy” skan CTE pokazuje się gorzej.

Testowanie wydajności

Mamy więc dwa sposoby serializacji danych w słowniku − hstore/json_object. Ponadto tablice samych kluczy i wartości można również generować na dwa sposoby, z wewnętrzną lub zewnętrzną konwersją na tekst: array_agg(i::text) / array_agg(i)::text[].

Sprawdźmy skuteczność różnych typów serializacji na czysto syntetycznym przykładzie - serializować różne liczby kluczy:

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

Skrypt ewaluacyjny: serializacja

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;

Antywzorce PostgreSQL: Słownik Hit Heavy JOIN

W PostgreSQL 11 do rozmiaru słownika wynoszącego w przybliżeniu 2^12 kluczy serializacja do jsona zajmuje mniej czasu. W tym przypadku najskuteczniejsze jest połączenie konwersji typu json_object i „wewnętrznej”. array_agg(i::text).

Teraz spróbujmy przeczytać wartość każdego klucza 8 razy - w końcu, jeśli nie masz dostępu do słownika, to po co ci to?

Skrypt ewaluacyjny: czytanie ze słownika

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;

Antywzorce PostgreSQL: Słownik Hit Heavy JOIN

I... już mniej więcej przy kluczach 2^6 czytanie ze słownika json zaczyna tracić wiele razy czytanie z hstore, dla jsonb to samo dzieje się przy 2^9.

Wnioski końcowe:

  • jeśli musisz to zrobić DOŁĄCZ z wieloma powtarzającymi się rekordami — lepiej skorzystać ze „słownika” tabeli
  • jeśli oczekuje się Twojego słownika mały i niewiele z niego odczytasz - możesz użyć json[b]
  • we wszystkich innych przypadkach hstore + array_agg(i::text) będzie bardziej skuteczny

Źródło: www.habr.com

Dodaj komentarz