🥇PostgreSQL-antimønstre: la oss slå den tunge JOIN med ordboken

Vi fortsetter serien med artikler som er viet til studiet av lite kjente måter å forbedre ytelsen til "tilsynelatende enkle" PostgreSQL-spørringer:

Ikke tenk at jeg ikke liker JOIN så mye... :)

Men ofte uten det, viser forespørselen seg å være betydelig mer produktiv enn med den. Så i dag prøver vi bli kvitt ressurskrevende JOIN - ved hjelp av en ordbok.

Fra og med PostgreSQL 12 kan noen av situasjonene beskrevet nedenfor gjengis litt annerledes pga. standard ikke-materialisering CTE. Denne oppførselen kan tilbakestilles ved å spesifisere nøkkelen MATERIALIZED.

Mange "fakta" i et begrenset ordforråd

La oss ta en veldig reell applikasjonsoppgave - vi må vise en liste innkommende meldinger eller aktive oppgaver med avsendere:

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

I den abstrakte verden bør oppgaveforfattere være jevnt fordelt blant alle ansatte i organisasjonen vår, men i virkeligheten oppgaver kommer som regel fra et ganske begrenset antall personer - "fra ledelsen" opp i hierarkiet eller "fra underleverandører" fra naboavdelinger (analytikere, designere, markedsføring, ...).

La oss akseptere at i vår organisasjon på 1000 personer er det bare 20 forfattere (vanligvis enda færre) som setter oppgaver for hver spesifikke utøver og La oss bruke denne fagkunnskapenfor å øke hastigheten på den "tradisjonelle" spørringen.

Skriptgenerator

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

La oss vise de siste 100 oppgavene for en spesifikk utfø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 seg at 1/3 total tid og 3/4 avlesninger sider med data ble laget bare for å søke etter forfatteren 100 ganger - for hver utdataoppgave. Men det vet vi blant disse hundrevis bare 20 forskjellige – Er det mulig å bruke denne kunnskapen?

hstore-ordbok

La oss dra nytte hstore type for å generere en "ordbok" nøkkelverdi:

CREATE EXTENSION hstore

Vi trenger bare å legge inn forfatterens ID og hans navn i ordboken slik at vi kan trekke ut ved å bruke denne nøkkelen:

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

Brukt på å innhente informasjon om personer 2 ganger mindre tid og 7 ganger mindre datalesing! I tillegg til "vokabular", var det som også hjalp oss med å oppnå disse resultatene gjenfinning av masseposter fra bordet i en enkelt omgang ved hjelp av = ANY(ARRAY(...)).

Tabelloppføringer: Serialisering og deserialisering

Men hva om vi trenger å lagre ikke bare ett tekstfelt, men en hel oppføring i ordboken? I dette tilfellet vil PostgreSQLs evne hjelpe oss behandle en tabelloppføring som en enkelt verdi:

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

La oss se på hva som foregikk her:

Vi tok p som et alias for oppføringen for fullpersonstabellen og samlet en rekke av dem.
dette utvalget av opptak ble omarbeidet til en rekke tekststrenger (person[]::tekst[]) for å plassere den i hstore-ordboken som en rekke verdier.
Når vi mottar en relatert post, vil vi hentet fra ordboken med nøkkel som en tekststreng.
Vi trenger tekst gjøre om til en tabelltypeverdi person (for hvert bord opprettes det automatisk en type med samme navn).
"Utvid" den innskrevne posten til kolonner ved hjelp av (...).*.

json-ordbok

Men et slikt triks som vi brukte ovenfor vil ikke fungere hvis det ikke er noen tilsvarende tabelltype for å utføre "casting". Nøyaktig samme situasjon vil oppstå, og hvis vi prøver å bruke en CTE-rad, ikke en "ekte" tabell.

I dette tilfellet vil de hjelpe oss funksjoner for å jobbe 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 bemerkes at når vi beskriver målstrukturen, kan vi ikke liste opp alle feltene i kildestrengen, men bare de vi virkelig trenger. Hvis vi har en "innfødt" tabell, er det bedre å bruke funksjonen json_populate_record.

Vi har fortsatt tilgang til ordboken en gang, men json-[de]serialiseringskostnadene er ganske høye, derfor er det rimelig å bruke denne metoden bare i noen tilfeller når den "ærlige" CTE-skanningen viser seg dårligere.

Testing av ytelse

Så vi har to måter å serialisere data til en ordbok - hstore/json_object. I tillegg kan matrisene av nøkler og verdier selv også genereres på to måter, med intern eller ekstern konvertering til tekst: array_agg(i::tekst) / array_agg(i)::tekst[].

La oss sjekke effektiviteten til forskjellige typer serialisering ved å bruke et rent syntetisk eksempel - serialiser forskjellige antall nøkler:

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

Evalueringsmanus: 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, opptil omtrent en ordbokstørrelse på 2^12 nøkler serialisering til json tar mindre tid. I dette tilfellet er det mest effektive kombinasjonen av json_object og konvertering av "intern" type array_agg(i::text).

La oss nå prøve å lese verdien av hver nøkkel 8 ganger - tross alt, hvis du ikke får tilgang til ordboken, hvorfor er det nødvendig?

Evalueringsmanus: lesing fra en ordbok

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 omtrent med 2^6 taster begynner lesing fra en json-ordbok å miste flere ganger lesing fra hstore, for jsonb skjer det samme ved 2^9.

Endelige konklusjoner:

hvis du trenger å gjøre det BLI MED med flere gjentatte poster - det er bedre å bruke "ordbok" av tabellen

hvis din ordbok er forventet liten og du vil ikke lese mye fra den - du kan bruke json[b]

i alle andre tilfeller hstore + array_agg(i::text) vil være mer effektivt

Kilde: www.habr.com

PostgreSQL-antimønstre: la oss slå den tunge JOIN med en ordbok

Mange "fakta" i et begrenset ordforråd

hstore-ordbok

Tabelloppføringer: Serialisering og deserialisering

json-ordbok

Testing av ytelse

Legg til en kommentar Avbryt svar