ProHoster ➡ Բլոգ ➡ Ադմինիստրացում ➡ PostgreSQL Antipatterns: խփենք բառարանը ծանր JOIN- ի

PostgreSQL Antipatterns: խփենք բառարանը ծանր JOIN- ի

Մենք շարունակում ենք հոդվածների շարքը, consacré à l'étude des moyens peu connus d'améliorer les performances des requêtes « apparemment simples » sur PostgreSQL :

Ne pensez pas que je déteste tellement les JOIN… 🙂

Mais souvent, sans lui, la requête est sensiblement plus performante qu'avec. Donc aujourd'hui, nous allons essayer de nous débarrasser complètement du JOIN gourmand en ressources — à l'aide d'un dictionnaire.

PostgreSQL Antipatterns: խփենք բառարանը ծանր JOIN- ի

À partir de PostgreSQL 12, certaines des situations décrites ci-dessous peuvent se reproduire un peu différemment en raison de la non-matérialisation des CTE par défaut. Ce comportement peut être rétabli à l'ancien en spécifiant la clé Դա, հետեւաբար, նշանակում է, որ եթե մենք ինչ-որ տեղ հարցման մեջ տեսնում ենք CTE-ի ձեւավորում, և ճշտում ենք պլանում, ապա այդ հանգույցները կարելի է միացնել:.

Beaucoup de « faits » sur le dictionnaire limité

Prenons une tâche d'application tout à fait réelle — il faut afficher la liste des messages entrants ou des tâches actives avec les expéditeurs :

25.01 | Ivanov I.I. | Préparer une description du nouvel algorithme.
22.01 | Ivanov I.I. | Écrire un article sur Habr : une vie sans JOIN.
20.01 | Petrov P.P. | Aider à optimiser la requête.
18.01 | Ivanov I.I. | Écrire un article sur Habr : JOIN en tenant compte de la répartition des données.
16.01 | Petrov P.P. | Aider à optimiser la requête.

Dans un monde abstrait, les auteurs des tâches devraient se répartir uniformément entre tous les employés de notre organisation, mais en réalité les tâches proviennent généralement d'un nombre assez limité de personnes — « de la direction » en haut de la hiérarchie ou « des collègues » des départements voisins (analystes, designers, marketing, ...).

Prenons l'hypothèse que dans notre organisation, sur 1000 personnes, seuls 20 auteurs (habituellement même moins) atterrissent les tâches à chaque exécuteur spécifique et profitons de cette connaissance du sujet, pour accélérer la requête « traditionnelle ».

Générateur de script

-- employés
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);

-- tâches avec la répartition spécifiée
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);

Montrons les 100 dernières tâches pour un exécuteur spécifique :

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;

PostgreSQL Antipatterns: խփենք բառարանը ծանր JOIN- ի
[տեսնել explain.tensor.ru]

Դիտվում է, որ 1/3 du temps total et 3/4 des lectures des pages de données ont été faites uniquement pour chercher 100 fois l'auteur — pour chaque tâche affichée. Mais nous savons que parmi ces centaines il n'y a que 20 différentes — ne pourrait-on pas utiliser cette connaissance ?

dictionnaire hstore

Նվազագույնը կօգտագործենք type hstore pour générer un « dictionnaire » clé-valeur :

ՎԵՐՋԱՓԱԿԻ 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 -> բանալի
FROM
  T;

PostgreSQL Antipatterns: խփենք բառարանը ծանր JOIN- ի
[տեսնել 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;

Հանգստանանք, թե ինչ է տեղի ունեցել այստեղ:

  1. Մենք վերցրել ենք p ի качестве алиаса для полной записи таблицы person и собрали из них массива.
  2. Этот մասի տվյալների массива перекастовали в массив текстовых строк (person[]::text[]), чтобы поместить его в hstore-словарь в качестве массива значений.
  3. При получении связанной записи мы ее вытащили из словаря по ключу как текстовую строку.
  4. Текст нам нужно превратить в значение типа таблицы person (для каждой таблицы автоматически создается одноименный ей тип).
  5. «Развернули» типизованную запись в столбцы с помощью (...).*.

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-[դե]սերիալիզացիայի ծախսերը բավականին մեծ են, այդ պատճառով այնպես օգտվելը իմաստ ունի միայն որոշ դեպքերում, երբ « eerlijk» CTE Scan-ը վատ է ստացվում։

Արդյունավետությունը փորձարկում

Այսպիսով, մենք ստացել ենք երկու եղանակ՝ տվյալները բառարանում սերիականացնելու համար — 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 Antipatterns: խփենք բառարանը ծանր JOIN- ի

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 < (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;

PostgreSQL Antipatterns: խփենք բառարանը ծանր JOIN- ի

Եվ… արդեն մոտավորապես 2^6 բանալիների համար json-բառարանից ընթերցումը սկսում է բազմապատկվել hstore-ից, jsonb-ի համար դա նույն բանն է տեղի ունենում 2^9-ին:

Թեեւ եզրակացություններ՝

  • եթե անհրաժեշտ է անել JOIN բազմակի կրկնվող գրառուքներով — լավագույնը օգտագործել «ուսացումներ» աղյուսակ
  • եթե ձեր բառարանը սպասված է չափազանց փոքր է և կարդալու եք նրանից քիչ — կարելի է օգտագործել json[b]
  • մնացած բոլոր դեպքերում hstore + array_agg(i::text) պետք է ավելի արդյունավետ լինի

Ընտանիք: habr.com

Գնել հուսալի հյուրընկալում DDoS պաշտպանությամբ, VPS VDS սերվերներով 🔥 Գնել հուսալի հյուրընկալում DDoS պաշտպանությամբ, VPS VDS սերվերներով | ProHoster