🥇PostgreSQL Antipatterns: colpiamo con il dizionario sul pesante JOIN

Continuiamo la serie di articoli dedicati all'esplorazione di metodi poco noti per migliorare le performance di query "apparentemente semplici" in PostgreSQL:

Non pensate che non ami così tanto JOIN… 🙂

Ma spesso senza di esso la query risulta visibilmente più performante rispetto a con esso. Quindi oggi proveremo addirittura a liberarci dal costoso JOIN — con l'ausilio di un dizionario.

Antipatterns di PostgreSQL: colpiamo il dizionario con un pesante JOIN

A partire da PostgreSQL 12, parte delle situazioni descritte di seguito potrebbero manifestarsi in modo leggermente diverso a causa di non-materializzazione di CTE per impostazione predefinita. Questo comportamento può essere ripristinato a quello precedente specificando la chiave MATERIALIZZATO.

Molti «fatti» su un dizionario limitato

Prendiamo un compito applicativo piuttosto reale: è necessario generare un elenco di messaggi in entrata o di attività attive con i 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 tenendo conto della distribuzione dei dati.
16.01 | Petrov P.P. | Aiutare a ottimizzare la query.

Nel mondo astratto, gli autori delle attività si dovrebbero distribuire uniformemente tra tutti i dipendenti della nostra organizzazione, ma nella realtà le attività provengono, di norma, da un numero piuttosto limitato di persone — «dalla direzione» in alto nella gerarchia o «da colleghi» di reparti vicini (analisti, designer, marketing, …).

Consideriamo che nella nostra organizzazione su 1000 persone solo 20 autori (di solito anche meno) pongono attività a favore di ciascun specifico esecutore e utilizziamo questa conoscenza tematica, per accelerare 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);

-- attività con la 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 particolare esecutore:

SELEZIONA
  task.*
, person.name
DA
  task
LEFT JOIN
  person
    ON person.id = task.author_id
DOVE
  owner_id = 777
ORDINA PER
  task_date DESC
LIMIT 100;

Antipatterns di PostgreSQL: colpiamo il dizionario con un pesante JOIN
[guarda su explain.tensor.ru]

Quindi, risulta che 1/3 del tempo totale e 3/4 delle letture pagine di dati sono state create solo per cercare 100 volte l'autore — per ogni attività visualizzata. Ma sappiamo che tra questi cento ci sono solo 20 diversi — non si può usare questa conoscenza?

dizionario hstore

Approfondiamo il tipo hstore per generare un "dizionario" chiave-valore:

CREA ESTENSIONE hstore

Nel dizionario basta inserire l'ID dell'autore e il suo nome, per poi poterli estrarre tramite quella chiave:

-- formiamo la selezione obiettivo
CON T COME (
  SELEZIONA
    *
  DA
    task
  DOVE
    owner_id = 777
  ORDINA PER
    task_date DESC
  LIMIT 100
)
-- formiamo il dizionario per valori unici
, dict COME (
  SELEZIONA
    hstore( -- hstore(keys::text[], values::text[])
      array_agg(id)::text[]
    , array_agg(name)::text[]
    )
  DA
    person
  DOVE
    id = ANY(ARRAY(
      SELEZIONA DISTINTO
        author_id
      DA
        T
    ))
)
-- otteniamo i valori associati al dizionario
SELEZIONA
  *
, (TABELLA dict) -> author_id::text -- hstore -> chiave
DA
  T;

Antipatterns di PostgreSQL: colpiamo il dizionario con un pesante JOIN
[guarda su explain.tensor.ru]

Per ottenere informazioni sulle persone è stato speso 2 volte meno tempo e 7 volte meno dati letti! Oltre al "dizionare", questi risultati sono stati raggiunti anche grazie a estrazione massiva di record dalla tabella in un solo passaggio utilizzando = ANY(ARRAY(...)).

Record della tabella: serializzazione e deserializzazione

Ma cosa fare se dobbiamo salvare nel dizionario non un singolo campo di testo, ma un'intera registrazione? In questo caso ci aiuterà la capacità di PostgreSQL di trattare la registrazione di una tabella come un valore unico:

...
, dict COME (
  SELEZIONA
    hstore(
      array_agg(id)::text[]
    , array_agg(p)::text[] -- magia #1
    )
  DA
    person p
  DOVE
    ...
)
SELEZIONA
  *
, (((TABELLA dict) -> author_id::text)::person).* -- magia #2
DA
  T;

Analizziamo cosa stava succedendo qui:

Abbiamo preso p come alias per la registrazione completa della tabella person e abbiamo creato un array da esse.
Questo array di registrazioni è stato riconvertito in un array di stringhe di testo (person[]::text[]), per poterlo inserire nel dizionario hstore come array di valori.
Quando otteniamo la registrazione associata, la estraiamo dal dizionario tramite la chiave come stringa di testo.
Il testo deve essere trasformato in un valore di tipo tabella person (per ogni tabella viene automaticamente creato un tipo con lo stesso nome).
"Espandiamo" la registrazione tipizzata in colonne utilizzando (...).*.

dizionario json

Tuttavia, un trucco come quello che abbiamo usato sopra non funzionerà se non c'è un tipo di tabella corrispondente per effettuare il "casting". La stessa situazione si verificherà se proviamo a utilizzare come sorgente dati per la serializzazione una stringa CTE, e non una "vera" tabella.

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

... 
, p AS ( -- questo è già un CTE
  SELECT
    *
  FROM
    person
  WHERE
    ...
)
, dict AS (
  SELECT
    json_object( -- ora questo è già json
      array_agg(id)::text[]
    , array_agg(row_to_json(p))::text[] -- e all'interno 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) -- abbiamo riempito 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 effettivamente bisogno. Se abbiamo una tabella “nativa”, è 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 è ragionevole utilizzare questo metodo solo in alcuni casi, quando una scansione "onesta" di CTE si comporta peggio.

Testiamo le prestazioni

Quindi, abbiamo ottenuto 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 una conversione interna o esterna in testo: array_agg(i::text) / array_agg(i)::text[].

Verifichiamo l'efficacia dei diversi tipi di serializzazione in un esempio puramente sintetico — serializziamo un diverso numero 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;

Antipatterns di PostgreSQL: colpiamo il dizionario con un pesante JOIN

Su PostgreSQL 11, fino alla dimensione del dizionario di 2^12 chiavi serializzare in json richiede meno tempo. La combinazione più efficace è json_object e la trasformazione "interna" dei tipi array_agg(i::text).

Ora proviamo a leggere il valore di ogni chiave 8 volte: se non ci si rivolge al dizionario, a cosa serve?

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;

Antipatterns di PostgreSQL: colpiamo il dizionario con un pesante JOIN

E... già circa con 2^6 chiavi, la lettura dal dizionario json inizia a perdere notevolmente rispetto alla lettura da hstore; lo stesso accade per jsonb con 2^9.

Conclusioni finali:

se è necessario fare JOIN con record ripetuti più volte è meglio utilizzare la "dizionizzazione" della tabella

se il tuo dizionario è prevedibilmente piccolo e lo leggerai poco puoi utilizzare json[b]

in tutti gli altri casi hstore + array_agg(i::text) sarà più efficace

Fonte: habr.com