PostgreSQL Antipatterns: colpiamo con il dizionario i pesanti JOIN

Continuiamo la nostra serie di articoli dedicati all'esplorazione di metodi poco conosciuti per migliorare le performance di query che sembrano 'semplici' in PostgreSQL:

• raramente una registrazione arriva a metà JOIN
• JOIN di array senza risultati
• JOIN e OR dannosi
• CTE JOIN CTE

Non pensate che non ami così tanto JOIN... 🙂

Ma spesso, senza di esso, la query risulta significativamente più veloce rispetto a quando è presente. Quindi oggi proviamo a eliminare del tutto il costoso JOIN — con l'aiuto di un dizionario.

A partire da PostgreSQL 12, alcune delle situazioni descritte di seguito potrebbero comportarsi leggermente diversamente a causa di non-materializzazione del CTE per impostazione predefinita. Questo comportamento può essere ripristinato a quello precedente specificando l'opzione MATERIALIZED.

Molti 'fatti' riguardo un dizionario limitato

Prendiamo un compito pratico abbastanza reale: dobbiamo estrarre un elenco di messaggi in arrivo o attività attive dai mittenti:

25.01 | Ivanov I.I. | Preparare la descrizione di un nuovo algoritmo.
22.01 | Ivanov I.I. | Scrivere un articolo su Habr: vita senza JOIN.
20.01 | Petrov P.P. | Aiutare a ottimizzare la query.
18.01 | Ivanov I.I. | Scrivere un articolo su Habr: JOIN con considerazione della distribuzione dei dati.
16.01 | Petrov P.P. | Aiutare a ottimizzare la query.

Nel mondo astratto, gli autori dei compiti dovrebbero essere distribuiti uniformemente tra tutti i dipendenti della nostra organizzazione, ma in realtà i compiti provengono, in genere, da un numero piuttosto limitato di persone — 'dalla direzione' in alto nella gerarchia o 'da colleghi' di reparti vicini (analisi, design, marketing, ...).

Assumiamo che nella nostra organizzazione, su 1000 persone, solo 20 autori (di solito anche meno) formulano compiti per ogni singolo esecutore e utilizziamo questa conoscenza specifica, per velocizzare la query 'tradizionale'.

Generatore di script

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

-- compiti con distribuzione specificata
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 singolo esecutore:

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 su explain.tensor.ru]

Ciò significa che 1/3 del totale del tempo e 3/4 delle letture dati sono stati dedicati solo a cercare il mittente 100 volte — per ogni compito visualizzato. Ma sappiamo che tra questi cento ci sono solo 20 diversi — non possiamo utilizzare questa conoscenza?

dizionario hstore

Utilizziamo tipo hstore per generare un 'dizionario' chiave-valore:

CREATE EXTENSION hstore

Nel dizionario possiamo inserire sufficientemente l'ID dell'autore e il suo nome, così da poterli estrarre successivamente con quella chiave:

-- generiamo il campione target
WITH T AS (
  SELECT
    *
  FROM
    task
  WHERE
    owner_id = 777
  ORDER BY
    task_date DESC
  LIMIT 100
)
-- generiamo un dizionario per valori unici
, 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
    ))
)
-- otteniamo i valori correlati dal dizionario
SELECT
  *
, (TABLE dict) -> author_id::text -- hstore -> chiave
FROM
  T;

[guarda su explain.tensor.ru]

Il tempo impiegato per ottenere informazioni sulle persone è stato due volte inferiore e le letture dei dati sono diminuite di 7 volte! Oltre alla 'dizionarizzazione', questi risultati sono stati ottenuti anche grazie al recupero massivo di record dalla tabella in un'unica operazione utilizzando = ANY(ARRAY(...)).

Registrazioni della tabella: serializzazione e deserializzazione

Ma cosa fare se dobbiamo salvare nel dizionario non un singolo campo di testo, ma un intero record? In questo caso ci aiuta la capacità di PostgreSQL di trattare un record della tabella come un valore unico:

...
, dict AS (
  SELECT
    hstore(
      array_agg(id)::text[]
    , array_agg(p)::text[] -- magia #1
    )
  FROM
    person p
  WHERE
    ...
)
SELECT
  *
, (((TABLE dict) -> author_id::text)::person).* -- magia #2
FROM
  T;

Analizziamo cosa stava realmente accadendo:

1. Abbiamo preso p come alias per l'intero record della tabella person e abbiamo raccolto da essi un array.
2. Questo array di record è stato ricastato in un array di stringhe di testo (person[]::text[]), per poterlo inserire nel dizionario hstore come un array di valori.
3. Quando otteniamo il record correlato, lo estraiamo dal dizionario per chiave come stringa testuale.
4. Il testo deve essere trasformato in un valore di tipo tabella person (per ogni tabella viene automaticamente creato un tipo con lo stesso nome).
5. Abbiamo 'espanso' il record tipizzato in colonne utilizzando (...).*.

dizionario json

Ma un trucco come quello che abbiamo applicato sopra non funzionerà se non c'è un tipo di tabella corrispondente per effettuare il "casting". La stessa situazione si presenterà se cerchiamo di utilizzare una stringa CTE invece di una "vera" tabella.

In questo caso, ci aiuteranno funzioni per lavorare con json:

...
, p AS ( -- questo è già un CTE
  SELECT
    *
  FROM
    person
  WHERE
    ...
)
, dict AS (
  SELECT
    json_object( -- ora è già json
      array_agg(id)::text[]
    , array_agg(row_to_json(p))::text[] -- e all'interno del json per ogni riga
    )
  FROM
    p
)
SELECT
  *
FROM
  T
, LATERAL(
    SELECT
      *
    FROM
      json_to_record(
        ((TABLE dict) -> author_id::text)::json -- estratto dal dizionario come json
      ) AS j(name text, birth_date date) -- riempito con la struttura di cui abbiamo bisogno
  ) j;

Va notato che nella descrizione della struttura target possiamo elencare non tutti i campi della riga di origine, ma solo quelli di cui abbiamo veramente bisogno. Se abbiamo una "tabella madre", è meglio utilizzare la funzione json_populate_record.

L'accesso al dizionario avviene ancora una volta, ma i costi per la json-[de]serializzazione sono piuttosto elevati, quindi è consigliabile utilizzare questo metodo solo in alcuni casi, quando una scansione "onesta" del CTE si dimostra meno efficace.

Testiamo le prestazioni

Quindi abbiamo ottenuto due modi per serializzare i dati in un dizionario — hstore / json_object. Oltre a ciò, gli stessi array di chiavi e valori possono essere generati in due modi, con o senza una conversione interna a testo: array_agg(i::text) / array_agg(i)::text[].

Verifichiamo l'efficacia dei diversi tipi di serializzazione su un esempio puramente sintetico — serializziamo diverse quantità 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 2^12 chiavi la serializzazione in json richiede meno tempo. In questo caso, la combinazione più efficace è json_object e conversione "interna" dei tipi array_agg(i::text).

Ora proviamo a leggere il valore di ogni chiave 8 volte — dopo tutto, se non accediamo al dizionario, qual è il suo scopo?

Script di valutazione: lettura dal 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 < (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à a circa con 2^6 chiavi, la lettura dal dizionario json inizia a essere notevolmente meno efficiente rispetto alla lettura da hstore; per jsonb la stessa cosa accade a 2^9.

Conclusioni finali:

• se è necessario eseguire un JOIN con registrazioni ripetute più volte è meglio utilizzare la "dizionizzazione" della tabella
• se il tuo dizionario è prevedibilmente piccolo e leggerai solo poche volte da esso puoi utilizzare json[b]
• in tutti gli altri casi hstore + array_agg(i::text) sarà più efficace

Fonte: habr.com