ProHoster > Blog > uprava > PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

Nastavljamo seriju članaka posvećenih proučavanju malo poznatih načina poboljšanja izvedbe "naizgled jednostavnih" PostgreSQL upita:

Nemojte misliti da mi se JOIN ne sviđa toliko... :)

Ali često se bez njega zahtjev pokaže znatno produktivnijim nego s njim. Dakle, danas ćemo pokušati riješite se JOIN-a koji zahtijeva puno resursa - pomoću rječnika.

PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

Počevši od PostgreSQL 12, neke od dolje opisanih situacija mogu se reproducirati malo drugačije zbog zadani nematerijalizacijski CTE. Ovo se ponašanje može poništiti navođenjem ključa MATERIALIZED.

Puno "činjenica" u ograničenom rječniku

Uzmimo vrlo stvaran aplikacijski zadatak - trebamo prikazati popis dolazne poruke ili aktivni zadaci s pošiljateljima:

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

U apstraktnom svijetu, autori zadataka trebali bi biti ravnomjerno raspoređeni među svim zaposlenicima naše organizacije, ali u stvarnosti zadaci u pravilu dolaze od prilično ograničenog broja ljudi - “od menadžmenta” na hijerarhijskoj ljestvici ili “od kooperanata” iz susjednih odjela (analitičari, dizajneri, marketing, ...).

Prihvatimo da u našoj organizaciji od 1000 ljudi samo 20 autora (obično čak i manje) postavlja zadatke svakom konkretnom izvođaču i Iskoristimo ovo znanje o predmetukako bi se ubrzao "tradicionalni" upit.

Generator skripti

-- сотрудники
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žimo zadnjih 100 zadataka za određenog izvršitelja:

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
[pogledajte na expand.tensor.ru]

Ispada da 1/3 ukupnog vremena i 3/4 čitanja stranice podataka napravljene su samo za traženje autora 100 puta - za svaki izlazni zadatak. Ali znamo da među ovim stotinama samo 20 različitih - Je li moguće iskoristiti ovo znanje?

hstore-rječnik

Iskoristimo vrsta hstore za generiranje ključa/vrijednosti "rječnika":

CREATE EXTENSION hstore

Samo trebamo staviti ID autora i njegovo ime u rječnik kako bismo zatim mogli izdvojiti pomoću ovog ključa:

-- формируем целевую выборку
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
[pogledajte na expand.tensor.ru]

Potrošeno na dobivanje podataka o osobama 2 puta manje vremena i 7 puta manje pročitanih podataka! Osim “vokabulara”, ono što nam je također pomoglo u postizanju ovih rezultata je bulk record retrieval iz tablice u jednom prolazu pomoću = ANY(ARRAY(...)).

Unosi u tablicu: serijalizacija i deserijalizacija

Ali što ako trebamo spremiti ne samo jedno tekstno polje, već cijeli unos u rječnik? U ovom slučaju, sposobnost PostgreSQL-a će nam pomoći tretirati unos tablice kao jednu vrijednost:

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

Pogledajmo što se ovdje događalo:

  1. Uzeli smo p kao pseudonim za puni unos tablice osoba i sastavio niz njih.
  2. Ovo niz snimaka je preoblikovan na niz tekstualnih nizova (osoba[]::tekst[]) da ga smjestite u hstore rječnik kao niz vrijednosti.
  3. Kada primimo povezani zapis, mi izvučeno iz rječnika po ključu kao tekstualni niz.
  4. Trebamo tekst pretvoriti u vrijednost tipa tablice osoba (za svaku tablicu se automatski kreira tip istog naziva).
  5. "Proširite" upisani zapis u stupce pomoću (...).*.

json rječnik

Ali takav trik koji smo gore primijenili neće funkcionirati ako ne postoji odgovarajuća vrsta tablice za "casting". Točno ista situacija će se pojaviti, i ako pokušamo koristiti CTE redak, a ne "prava" tablica.

U ovom slučaju oni će nam pomoći funkcije za rad s 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;

Treba napomenuti da pri opisivanju ciljne strukture ne možemo navesti sva polja izvornog niza, već samo ona koja su nam stvarno potrebna. Ako imamo "nativnu" tablicu, onda je bolje koristiti funkciju json_populate_record.

Još jednom pristupamo rječniku, ali json-[de]serialization troškovi su prilično visoki, stoga je razumno koristiti ovu metodu samo u nekim slučajevima kada se "pošteni" CTE snimka pokazuje lošije.

Testiranje izvedbe

Dakle, imamo dva načina za serijalizaciju podataka u rječnik − hstore/json_object. Osim toga, sami nizovi ključeva i vrijednosti također se mogu generirati na dva načina, s unutarnjom ili vanjskom konverzijom u tekst: array_agg(i::text) / array_agg(i)::text[].

Provjerimo učinkovitost različitih vrsta serijalizacije koristeći čisto sintetički primjer - serijalizirati različite brojeve ključeva:

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

Evaluacijska skripta: serijalizacija

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

Na PostgreSQL 11, otprilike do veličine rječnika od 2^12 ključeva serijalizacija u json traje manje vremena. U ovom slučaju najučinkovitija je kombinacija json_object i “interne” konverzije tipa array_agg(i::text).

Pokušajmo sada pročitati vrijednost svakog ključa 8 puta - uostalom, ako ne pristupite rječniku, zašto je onda potreban?

Skripta za ocjenjivanje: čitanje iz rječnika

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

I... već otprilike s 2^6 ključeva, čitanje iz json rječnika počinje gubiti više puta čitanje iz hstorea, za jsonb isto se događa na 2^9.

Konačni zaključci:

  • ako to trebate učiniti PRIDRUŽITE SE s više zapisa koji se ponavljaju — bolje je koristiti "rječnik" tablice
  • ako se vaš rječnik očekuje malen i nećete puno pročitati iz njega - možete koristiti json[b]
  • u svim ostalim slučajevima hstore + array_agg(i::text) će biti učinkovitiji

Izvor: www.habr.com

Dodajte komentar