🥇PostgreSQL Antimønstre: lad os slå den tunge JOIN med ordbogen

Vi fortsætter serien af artikler, der er viet til undersøgelsen af lidt kendte måder at forbedre ydeevnen af "tilsyneladende simple" PostgreSQL-forespørgsler:

Tro ikke, at jeg ikke kan lide JOIN så meget... :)

Men ofte uden det, viser anmodningen sig at være væsentligt mere produktiv end med den. Så i dag prøver vi slippe af med ressourcekrævende JOIN - ved hjælp af en ordbog.

Startende med PostgreSQL 12 kan nogle af de situationer, der er beskrevet nedenfor, blive gengivet lidt anderledes pga. standard ikke-materialisering CTE. Denne adfærd kan vendes tilbage ved at angive nøglen MATERIALIZED.

Masser af "fakta" i et begrænset ordforråd

Lad os tage en meget reel ansøgningsopgave - vi skal vise en liste indgående beskeder eller aktive opgaver med afsendere:

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

I den abstrakte verden bør opgaveforfattere være jævnt fordelt blandt alle medarbejdere i vores organisation, men i virkeligheden opgaver kommer som udgangspunkt fra et ret begrænset antal personer - "fra ledelsen" op i hierarkiet eller "fra underleverandører" fra naboafdelinger (analytikere, designere, marketing, ...).

Lad os acceptere, at i vores organisation på 1000 mennesker er det kun 20 forfattere (som regel endnu færre) der sætter opgaver for hver specifik performer og Lad os bruge denne fagvidenfor at fremskynde den "traditionelle" forespørgsel.

Script generator

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

Lad os vise de sidste 100 opgaver for en specifik udfører:

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;

[se på explain.tensor.ru]

Det viser sig, at 1/3 samlet tid og 3/4 aflæsninger sider med data blev kun lavet for at søge efter forfatteren 100 gange - for hver outputopgave. Men det ved vi blandt disse hundreder kun 20 forskellige - Er det muligt at bruge denne viden?

hstore-ordbog

Lad os drage fordel hstore type for at generere en "ordbog" nøgleværdi:

CREATE EXTENSION hstore

Vi skal blot indsætte forfatterens ID og hans navn i ordbogen, så vi derefter kan udtrække ved hjælp af denne nøgle:

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

[se på explain.tensor.ru]

Brugt på at indhente oplysninger om personer 2 gange mindre tid og 7 gange mindre datalæsning! Ud over "ordforråd", var det, der også hjalp os med at opnå disse resultater hentning af masseregistreringer fra bordet i en enkelt omgang vha = ANY(ARRAY(...)).

Tabelposter: Serialisering og Deserialisering

Men hvad nu hvis vi skal gemme ikke kun et tekstfelt, men en hel post i ordbogen? I dette tilfælde vil PostgreSQL's evner hjælpe os behandle en tabelpost som en enkelt værdi:

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

Lad os se på, hvad der foregik her:

Vi tog p som et alias til indtastningen af fuldpersonstabellen og samlede en række af dem.
dette rækken af optagelser blev omarbejdet til en række tekststrenge (person[]::tekst[]) for at placere den i hstore-ordbogen som en række værdier.
Når vi modtager en relateret post, vi hentet fra ordbogen med nøgle som en tekststreng.
Vi har brug for tekst omdannes til en tabeltypeværdi person (for hvert bord oprettes automatisk en type med samme navn).
"Udvid" den indtastede post til kolonner vha (...).*.

json ordbog

Men sådan et trick, som vi anvendte ovenfor, vil ikke fungere, hvis der ikke er en tilsvarende tabeltype til at udføre "casting". Nøjagtig den samme situation vil opstå, og hvis vi forsøger at bruge en CTE-række, ikke en "rigtig" tabel.

I dette tilfælde vil de hjælpe os funktioner til at arbejde med 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;

Det skal bemærkes, at når vi beskriver målstrukturen, kan vi ikke liste alle felterne i kildestrengen, men kun dem, vi virkelig har brug for. Hvis vi har en "native" tabel, så er det bedre at bruge funktionen json_populate_record.

Vi har stadig adgang til ordbogen én gang, men omkostningerne til json-[de]serialisering er ret høje, derfor er det rimeligt kun at bruge denne metode i nogle tilfælde, når den "ærlige" CTE-scanning viser sig dårligere.

Test af ydeevne

Så vi har to måder at serialisere data til en ordbog - hstore/json_object. Derudover kan arrays af nøgler og værdier selv også genereres på to måder, med intern eller ekstern konvertering til tekst: array_agg(i::tekst) / array_agg(i)::tekst[].

Lad os tjekke effektiviteten af forskellige typer serialisering ved hjælp af et rent syntetisk eksempel - serialisere forskellige antal nøgler:

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

Evalueringsmanuskript: serialisering

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;

På PostgreSQL 11, op til cirka en ordbogsstørrelse på 2^12 nøgler serialisering til json tager mindre tid. I dette tilfælde er den mest effektive kombinationen af json_object og "intern" type konvertering array_agg(i::text).

Lad os nu prøve at læse værdien af hver nøgle 8 gange - når alt kommer til alt, hvis du ikke får adgang til ordbogen, hvorfor er det så nødvendigt?

Evalueringsmanuskript: læsning fra en ordbog

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;

Og... allerede cirka med 2^6 nøgler begynder læsning fra en json-ordbog at tabe flere gange læsning fra hstore, for jsonb sker det samme ved 2^9.

Endelige konklusioner:

hvis du skal gøre det JOIN med flere gentagne poster - det er bedre at bruge "ordbog" af tabellen

hvis din ordbog forventes lille, og du vil ikke læse meget fra den - du kan bruge json[b]

i alle andre tilfælde hstore + array_agg(i::text) vil være mere effektiv

Kilde: www.habr.com

PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

Masser af "fakta" i et begrænset ordforråd

hstore-ordbog

Tabelposter: Serialisering og Deserialisering

json ordbog

Test af ydeevne

Tilføj en kommentar Annuller svar