ProHoster > Blogs > Administrācija > PostgreSQL antipatterns: Dictionary Hit Heavy JOIN

PostgreSQL antipatterns: Dictionary Hit Heavy JOIN

Mēs turpinām rakstu sēriju, kas veltīta mazpazīstamu veidu izpētei, kā uzlabot “šķietami vienkāršu” PostgreSQL vaicājumu veiktspēju:

Nedomājiet, ka man tik ļoti nepatīk JOIN... :)

Bet bieži vien bez tā pieprasījums izrādās ievērojami produktīvāks nekā ar to. Tāpēc šodien mēģināsim atbrīvoties no resursietilpīga JOIN - izmantojot vārdnīcu.

PostgreSQL antipatterns: Dictionary Hit Heavy JOIN

Sākot ar PostgreSQL 12, dažas no tālāk aprakstītajām situācijām var tikt reproducētas nedaudz atšķirīgi, jo noklusējuma nematerializācija CTE. Šo darbību var atsaukt, norādot atslēgu MATERIALIZED.

Daudz “faktu” ierobežotā vārdu krājumā

Ņemsim ļoti reālu lietojumprogrammas uzdevumu - mums ir jāparāda saraksts ienākošās ziņas vai aktīvi uzdevumi ar sūtītājiem:

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

Abstraktajā pasaulē uzdevumu autoriem vajadzētu būt vienmērīgi sadalītiem starp visiem mūsu organizācijas darbiniekiem, bet patiesībā uzdevumus, kā likums, veic diezgan ierobežots cilvēku skaits - "no vadības" uz augšu pa hierarhiju vai "no apakšuzņēmējiem" no blakus esošajām nodaļām (analītiķi, dizaineri, mārketings, ...).

Pieņemsim, ka mūsu 1000 cilvēku organizācijā tikai 20 autori (parasti pat mazāk) izvirza uzdevumus katram konkrētajam izpildītājam un Izmantosim šīs priekšmeta zināšanaslai paātrinātu "tradicionālo" vaicājumu.

Skriptu ģenerators

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

Parādīsim pēdējos 100 uzdevumus konkrētam izpildītājam:

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: Dictionary Hit Heavy JOIN
[apskatiet skaidro.tensor.ru]

Izrādās, ka 1/3 kopējā laika un 3/4 rādījumu datu lapas tika veiktas tikai, lai meklētu autoru 100 reizes - katram izvades uzdevumam. Bet mēs zinām, ka starp šiem simtiem tikai 20 dažādi – Vai šīs zināšanas ir iespējams izmantot?

hstore-vārdnīca

Izmantosim priekšrocības hstore tips lai ģenerētu “vārdnīcas” atslēgas vērtību:

CREATE EXTENSION hstore

Mums vienkārši jāievieto vārdnīcā autora ID un viņa vārds, lai pēc tam varētu izvilkt, izmantojot šo atslēgu:

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

PostgreSQL antipatterns: Dictionary Hit Heavy JOIN
[apskatiet skaidro.tensor.ru]

Iztērēti informācijas iegūšanai par personām 2 reizes mazāk laika un 7 reizes mazāk nolasīto datu! Papildus “vārdu krājumam” arī tas, kas mums palīdzēja sasniegt šos rezultātus, bija lielapjoma ierakstu izguve no galda vienā piegājienā, izmantojot = ANY(ARRAY(...)).

Tabulas ieraksti: Serializācija un Deserializācija

Bet ko darīt, ja mums jāsaglabā ne tikai viens teksta lauks, bet viss vārdnīcas ieraksts? Šajā gadījumā PostgreSQL spēja mums palīdzēs apstrādāt tabulas ierakstu kā vienu vērtību:

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

Apskatīsim, kas šeit notika:

  1. Mēs paņēmām p kā aizstājvārdu pilnas personas tabulas ierakstam un apkopoja to masīvu.
  2. Šis ierakstu masīvs tika pārstrādāts teksta virkņu masīvam (person[]::teksts[]), lai ievietotu to hstore vārdnīcā kā vērtību masīvu.
  3. Kad saņemam saistītu ierakstu, mēs izvilkts no vārdnīcas pēc atslēgas kā teksta virkne.
  4. Mums vajag tekstu pārvērst par tabulas tipa vērtību persona (katrai tabulai automātiski tiek izveidots tāda paša nosaukuma tips).
  5. “Izvērsiet” ierakstīto ierakstu kolonnās, izmantojot (...).*.

json vārdnīca

Bet šāds triks, ko izmantojām iepriekš, nedarbosies, ja nav atbilstoša tabulas veida, lai veiktu “casting”. Tieši tāda pati situācija radīsies, un, ja mēs mēģināsim izmantot CTE rinda, nevis "īsta" tabula.

Šajā gadījumā viņi mums palīdzēs funkcijas darbam ar 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;

Jāatzīmē, ka, aprakstot mērķa struktūru, mēs nevaram uzskaitīt visus avota virknes laukus, bet tikai tos, kas mums patiešām ir nepieciešami. Ja mums ir “native” tabula, tad labāk ir izmantot funkciju json_populate_record.

Mēs joprojām piekļūstam vārdnīcai vienreiz, bet json-[de]serializācijas izmaksas ir diezgan augstas, tāpēc šo metodi ir saprātīgi izmantot tikai atsevišķos gadījumos, kad “godīgais” CTE Scan sevi parāda sliktāk.

Testēšanas veiktspēja

Tātad, mums ir divi veidi, kā serializēt datus vārdnīcā - hstore/json_object. Turklāt pašus atslēgu un vērtību masīvus var ģenerēt arī divos veidos, veicot iekšēju vai ārēju pārveidošanu par tekstu: masīvs_agg(i::teksts) / masīvs_agg(i)::teksts[].

Pārbaudīsim dažādu serializācijas veidu efektivitāti, izmantojot tīri sintētisku piemēru - serializēt dažādus atslēgu numurus:

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

Novērtēšanas skripts: serializācija

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: Dictionary Hit Heavy JOIN

Operētājsistēmā PostgreSQL 11 — līdz aptuveni vārdnīcas lielumam 2^12 atslēgas serializācija uz JSON aizņem mazāk laika. Šajā gadījumā visefektīvākā ir json_object un “iekšējā” tipa konvertēšanas kombinācija array_agg(i::text).

Tagad mēģināsim nolasīt katras atslēgas vērtību 8 reizes - galu galā, ja jūs nepiekļūstat vārdnīcai, tad kāpēc tas ir vajadzīgs?

Novērtēšanas skripts: lasīšana no vārdnīcas

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;

PostgreSQL antipatterns: Dictionary Hit Heavy JOIN

Un... jau aptuveni ar 2^6 taustiņiem, lasīšana no json vārdnīcas sāk zaudēt vairākas reizes lasot no hstore, jsonb tas pats notiek pie 2^9.

Galīgie secinājumi:

  • ja jums tas jādara PIEVIENOJIES ar vairākiem atkārtotiem ierakstiem — labāk izmantot tabulas “vārdnīcu”.
  • ja tiek gaidīta jūsu vārdnīca mazs un no tā neko daudz neizlasīsi - varat izmantot json[b]
  • visos citos gadījumos hstore + array_agg(i::text) būs efektīvāka

Avots: www.habr.com

Pievieno komentāru