Antipattern PostgreSQL: Dictionary Hit Heavy JOIN

Continuiamo la serie di articoli dedicati allo studio di modi poco conosciuti per migliorare le prestazioni di query PostgreSQL “apparentemente semplici”:

• il record raro raggiungerà la metà di JOIN
• Array JOIN di Sisifo
• JOIN e OR dannosi
• CTE UNISCITI A CTE

Non pensare che non mi piaccia così tanto ISCRIVITI... :)

Ma spesso senza di essa la richiesta risulta essere significativamente più produttiva che con essa. Quindi oggi ci proveremo sbarazzarsi di JOIN ad alta intensità di risorse - utilizzando un dizionario.

A partire da PostgreSQL 12, alcune delle situazioni descritte di seguito potrebbero essere riprodotte in modo leggermente diverso a causa di CTE di non materializzazione predefinita. Questo comportamento può essere ripristinato specificando la chiave MATERIALIZED.

Molti “fatti” in un vocabolario limitato

Prendiamo un compito applicativo molto reale: dobbiamo visualizzare un elenco messaggi in arrivo o attività attive con mittenti:

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

Nel mondo astratto, gli autori delle attività dovrebbero essere distribuiti equamente tra tutti i dipendenti della nostra organizzazione, ma nella realtà i compiti provengono, di regola, da un numero abbastanza limitato di persone - “dalla direzione” più in alto nella gerarchia o “da subappaltatori” di dipartimenti vicini (analisti, progettisti, marketing, ...).

Ammettiamo che nella nostra organizzazione di 1000 persone, solo 20 autori (di solito anche meno) fissino i compiti per ogni specifico interprete e Usiamo questa conoscenza dell'argomentoper velocizzare l'interrogazione "tradizionale".

Generatore di script

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

Mostriamo le ultime 100 attività per un esecutore specifico:

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;

[Guarda spiegare.tensor.ru]

Si scopre che 1/3 del tempo totale e 3/4 letture le pagine di dati sono state create solo per cercare l'autore 100 volte, per ciascuna attività di output. Ma sappiamo che tra queste centinaia solo 20 diversi - È possibile utilizzare questa conoscenza?

dizionario-hstore

Usiamo tipo hstore per generare un valore-chiave “dizionario”:

CREATE EXTENSION hstore

Dobbiamo solo inserire l'ID dell'autore e il suo nome nel dizionario per poter poi estrarre utilizzando questa chiave:

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

[Guarda spiegare.tensor.ru]

Speso per ottenere informazioni sulle persone 2 volte meno tempo e 7 volte meno dati letti! Oltre al “vocabolario”, ciò che ci ha aiutato a raggiungere questi risultati è stato recupero di record in blocco dal tavolo in un unico passaggio utilizzando = ANY(ARRAY(...)).

Voci della tabella: serializzazione e deserializzazione

Ma cosa succede se dobbiamo salvare non solo un campo di testo, ma un'intera voce del dizionario? In questo caso, l'abilità di PostgreSQL ci aiuterà trattare una voce di tabella come un singolo valore:

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

Diamo un'occhiata a cosa stava succedendo qui:

1. Abbiamo preso p come alias della voce completa della tabella delle persone e ne radunò una schiera.
2. questo la serie di registrazioni è stata rifusa in un array di stringhe di testo (person[]::text[]) per inserirlo nel dizionario hstore come array di valori.
3. Quando riceviamo un record correlato, noi estratto dal dizionario tramite chiave come stringa di testo.
4. Abbiamo bisogno di testo trasformarsi in un valore di tipo tabella persona (per ogni tabella viene creata automaticamente una tipologia con lo stesso nome).
5. "Espandi" il record digitato in colonne utilizzando (...).*.

dizionario json

Ma un trucco come quello che abbiamo applicato sopra non funzionerà se non esiste un tipo di tabella corrispondente per eseguire il “casting”. Si presenterà esattamente la stessa situazione e se proviamo a usarla una riga CTE, non una tabella "reale"..

In questo caso ci aiuteranno funzioni per lavorare con 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;

Va notato che quando descriviamo la struttura di destinazione, non possiamo elencare tutti i campi della stringa sorgente, ma solo quelli di cui abbiamo realmente bisogno. Se abbiamo una tabella “nativa”, è meglio utilizzare la funzione json_populate_record.

Accediamo ancora al dizionario una volta, ma I costi di json-[de]serializzazione sono piuttosto elevati, pertanto, è ragionevole utilizzare questo metodo solo in alcuni casi quando la scansione CTE “onesta” si rivela peggiore.

Testare le prestazioni

Quindi, abbiamo due modi per serializzare i dati in un dizionario − hstore/json_object. Inoltre, gli stessi array di chiavi e valori possono essere generati anche in due modi, con conversione interna o esterna in testo: array_agg(i::testo) / array_agg(i)::testo[].

Verifichiamo l'efficacia dei diversi tipi di serializzazione utilizzando un esempio puramente sintetico: serializzare diversi numeri di chiavi:

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

Script di valutazione: serializzazione

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;

Su PostgreSQL 11, fino a una dimensione del dizionario di circa 2^12 chiavi la serializzazione su JSON richiede meno tempo. In questo caso, la più efficace è la combinazione di json_object e conversione di tipo “internal”. array_agg(i::text).

Ora proviamo a leggere il valore di ciascuna chiave 8 volte: dopo tutto, se non accedi al dizionario, perché è necessario?

Script di valutazione: lettura da un dizionario

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;

E... già approssimativamente con 2 ^ 6 chiavi, la lettura da un dizionario json inizia a perdere più volte leggendo da hstore, per jsonb lo stesso accade in 2^9.

Conclusioni finali:

• se hai bisogno di farlo UNISCITI con più record ripetuti — è meglio usare il “dizionario” della tabella
• se è previsto il tuo dizionario piccolo e non ne leggerai molto - puoi usare json[b]
• in tutti gli altri casi hstore + array_agg(i::testo) sarà più efficace

Fonte: habr.com