PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

Vi fortsätter serien av artiklar som ägnas åt studier av föga kända sätt att förbättra prestandan för "till synes enkla" PostgreSQL-frågor:

• sällsynt rekord kommer att nå mitten av JOIN
• Sisyphean JOIN-arrayer
• skadliga JOIN och OR
• CTE GÅ MED I CTE

Tro inte att jag inte gillar JOIN så mycket... :)

Men ofta utan det visar sig begäran vara betydligt mer produktiv än med den. Så idag ska vi försöka bli av med resurskrävande JOIN - med hjälp av en ordbok.

Från och med PostgreSQL 12 kan vissa av de situationer som beskrivs nedan återges något annorlunda p.g.a. standard icke-materialisering CTE. Detta beteende kan återställas genom att ange nyckeln MATERIALIZED.

Mycket "fakta" i ett begränsat ordförråd

Låt oss ta en mycket verklig applikationsuppgift - vi måste visa en lista inkommande meddelanden eller aktiva uppgifter med avsändare:

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

I den abstrakta världen bör uppgiftsförfattare vara jämnt fördelade bland alla anställda i vår organisation, men i verkligheten arbetsuppgifterna kommer i regel från ett ganska begränsat antal personer - "från ledningen" upp i hierarkin eller "från underleverantörer" från närliggande avdelningar (analytiker, designers, marknadsföring, ...).

Låt oss acceptera att i vår organisation på 1000 personer är det bara 20 författare (vanligtvis ännu färre) som sätter uppgifter för varje specifik artist och Låt oss använda denna ämneskunskapför att påskynda den "traditionella" frågan.

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

Låt oss visa de senaste 100 uppgifterna för en specifik utförare:

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;

[titta på explain.tensor.ru]

Det visar sig att 1/3 total tid och 3/4 avläsningar sidor med data gjordes bara för att söka efter författaren 100 gånger - för varje utdatauppgift. Men det vet vi bland dessa hundra bara 20 olika – Är det möjligt att använda den här kunskapen?

hstore-ordbok

Låt oss dra fördel hstore typ för att generera ett "ordbok" nyckel-värde:

CREATE EXTENSION hstore

Vi behöver bara lägga in författarens ID och hans namn i ordboken så att vi sedan kan extrahera med denna nyckel:

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

[titta på explain.tensor.ru]

Spenderat på att skaffa information om personer 2 gånger mindre tid och 7 gånger mindre läsdata! Förutom "vokabulär" var det som också hjälpte oss att uppnå dessa resultat hämtning av massuppteckningar från bordet i ett enda pass med hjälp av = ANY(ARRAY(...)).

Tabellposter: Serialisering och Deserialisering

Men vad händer om vi behöver spara inte bara ett textfält, utan en hel post i ordboken? I det här fallet kommer PostgreSQL:s förmåga att hjälpa oss behandla en tabellpost som ett enda värde:

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

Låt oss titta på vad som pågick här:

1. Vi tog p som ett alias till fullpersonstabellposten och samlade en rad av dem.
2. detta utbudet av inspelningar gjordes om till en array av textsträngar (person[]::text[]) för att placera den i hstore-ordboken som en array av värden.
3. När vi får en relaterad post, vi hämtas från ordboken med nyckel som en textsträng.
4. Vi behöver text förvandlas till ett tabelltypvärde person (för varje bord skapas automatiskt en typ med samma namn).
5. "Utöka" den inskrivna posten till kolumner med hjälp av (...).*.

json ordbok

Men ett sådant trick som vi tillämpade ovan kommer inte att fungera om det inte finns någon motsvarande tabelltyp för att göra "casting". Exakt samma situation kommer att uppstå, och om vi försöker använda en CTE-rad, inte en "riktig" tabell.

I det här fallet kommer de att hjälpa oss funktioner för att arbeta 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 bör noteras att när vi beskriver målstrukturen kan vi inte lista alla fält i källsträngen, utan bara de som vi verkligen behöver. Om vi ​​har en "inbyggd" tabell är det bättre att använda funktionen json_populate_record.

Vi har fortfarande tillgång till ordboken en gång, men json-[de]serialiseringskostnaderna är ganska höga, därför är det rimligt att använda denna metod endast i vissa fall när den "ärliga" CTE-skanningen visar sig sämre.

Testar prestanda

Så vi har två sätt att serialisera data till en ordbok - hstore/json_object. Dessutom kan arrayerna av nycklar och värden själva också genereras på två sätt, med intern eller extern konvertering till text: array_agg(i::text) / array_agg(i)::text[].

Låt oss kontrollera effektiviteten av olika typer av serialisering med ett rent syntetiskt exempel - serialisera olika antal nycklar:

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

Utvärderingsmanus: 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, upp till ungefär en ordboksstorlek på 2^12 nycklar serialisering till json tar mindre tid. I det här fallet är den mest effektiva kombinationen av json_object och "intern" typkonvertering array_agg(i::text).

Låt oss nu försöka läsa värdet på varje nyckel 8 gånger - trots allt, om du inte kommer åt ordboken, varför behövs den då?

Utvärderingsmanus: läsning ur 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;

Och... redan ungefär med 2^6 nycklar, börjar läsning från en json-ordbok att förlora flera gånger läser från hstore, för jsonb händer samma sak vid 2^9.

Slutsatser:

• om du behöver göra det JOIN med flera upprepade poster - det är bättre att använda "ordbok" i tabellen
• om din ordbok förväntas liten och du kommer inte att läsa mycket från den - du kan använda json[b]
• i alla andra fall hstore + array_agg(i::text) kommer att vara effektivare

Källa: will.com