🥇PostgreSQL-i antimustrid: lööme sõnastikuga JOIN

Jätkame artiklite seeriat, mis on pühendatud vähetuntud viiside uurimisele, kuidas "näiliselt lihtsate" PostgreSQL-päringute toimivust parandada:

Ärge arvake, et mulle JOIN nii väga ei meeldi... :)

Kuid sageli ilma selleta osutub taotlus oluliselt produktiivsemaks kui sellega. Nii et täna proovime vabaneda ressursimahukast LIITUMIST - sõnaraamatu kasutamine.

Alates versioonist PostgreSQL 12 võivad mõned allpool kirjeldatud olukorrad esineda veidi erinevalt, kuna vaikimisi mittematerialiseerimine CTE. Seda käitumist saab võtme määramisega taastada MATERIALIZED.

Piiratud sõnavaras palju "fakte".

Võtame väga reaalse rakendusülesande – peame kuvama loendi sissetulevad sõnumid või aktiivsed ülesanded saatjatega:

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

Abstraktses maailmas peaksid ülesannete autorid olema ühtlaselt jaotunud kõigi meie organisatsiooni töötajate vahel, kuid tegelikkuses ülesanded tulevad reeglina üsna piiratud arvult inimestelt - "juhtkonnast" hierarhias ülespoole või "allhankijatelt" naaberosakondadest (analüütikud, disainerid, turundus jne).

Leppigem sellega, et meie 1000 inimesega organisatsioonis seab igale konkreetsele esinejale ülesandeid vaid 20 autorit (tavaliselt isegi vähem). Kasutame neid aineteadmisi"traditsioonilise" päringu kiirendamiseks.

Skripti generaator

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

Näitame konkreetse täitja 100 viimast ülesannet:

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;

[vaadake saidil magyarázat.tensor.ru]

Selgub, et 1/3 koguajast ja 3/4 näitu lehekülgi andmeid tehti ainult selleks, et otsida autorit 100 korda – iga väljundülesande kohta. Kuid me teame seda nende sadade seas ainult 20 erinevat - Kas neid teadmisi on võimalik kasutada?

hstore-sõnastik

Kasutame ära hstore tüüp sõnastiku võtmeväärtuse loomiseks:

CREATE EXTENSION hstore

Peame lihtsalt sõnastikku sisestama autori ID ja tema nime, et saaksime selle võtme abil välja võtta:

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

[vaadake saidil magyarázat.tensor.ru]

Kulutatud isikute kohta teabe hankimisele 2 korda vähem aega ja 7 korda vähem andmeid loetakse! Lisaks "sõnavarale" aitas meil neid tulemusi saavutada ka hulgikirjete otsimine tabelist ühe käiguga kasutades = ANY(ARRAY(...)).

Tabelikirjed: serialiseerimine ja deserialiseerimine

Aga mis siis, kui peame salvestama mitte ainult ühe tekstivälja, vaid kogu sõnaraamatu kirje? Sel juhul aitab meid PostgreSQL-i võime käsitleda tabelikirjet ühe väärtusena:

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

Vaatame, mis siin toimus:

Võtsime p täisisiku tabeli kirje pseudonüümina ja koostas neist hulga.
see salvestiste massiiv sõnastati uuesti tekstistringide massiivi (person[]::text[]), et asetada see väärtuste massiivina hstore'i sõnastikku.
Kui saame seotud kirje, siis me võtmega sõnastikust tõmmatud tekstistringina.
Vajame teksti muuta tabelitüübi väärtuseks isik (iga tabeli jaoks luuakse automaatselt samanimeline tüüp).
"Laiendage" sisestatud kirje veergudeks, kasutades (...).*.

jsoni sõnastik

Kuid selline nipp, nagu me ülalpool rakendasime, ei tööta, kui pole sobivat tabelitüüpi "valamiseks". Täpselt sama olukord tekib ja kui proovime kasutada CTE rida, mitte "päris" tabel.

Sel juhul aitavad nad meid funktsioonid jsoniga töötamiseks:

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

Tuleb märkida, et sihtstruktuuri kirjeldamisel ei saa me loetleda kõiki lähtestringi välju, vaid ainult neid, mida me tõesti vajame. Kui meil on "native" tabel, siis on parem kasutada funktsiooni json_populate_record.

Sõnaraamatusse jõuame ikka korra, aga json-[de]serialiseerimise kulud on üsna suured, seetõttu on seda meetodit mõistlik kasutada vaid mõnel juhul, kui “aus” CTE-skannimine näitab end kehvemini.

Toimivuse testimine

Niisiis, meil on kaks võimalust andmete jadasõnasse viimiseks − hstore/json_object. Lisaks saab võtmete ja väärtuste massiive genereerida ka kahel viisil, sisemise või välise teisendamisega tekstiks: massiiv_agg(i::tekst) / massiiv_agg(i)::tekst[].

Kontrollime eri tüüpi serialiseerimise tõhusust puhtalt sünteetilise näite abil - serialiseerida erinevad numbrid võtmed:

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

Hindamisskript: serialiseerimine

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;

PostgreSQL 11 puhul kuni ligikaudu 2^12 klahvi suurune sõnastik json-i serialiseerimine võtab vähem aega. Sel juhul on kõige tõhusam json_object ja "sisemise" tüüpi teisenduse kombinatsioon array_agg(i::text).

Proovime nüüd iga võtme väärtust 8 korda lugeda – lõppude lõpuks, kui te ei pääse sõnaraamatusse, siis milleks seda vaja on?

Hindamisskript: lugemine sõnastikust

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;

Ja... juba ligikaudu 2^6 klahviga hakkab jsoni sõnastikust lugemine mitu korda kaduma lugedes hstore'ist, jsonb puhul juhtub sama 2^9 juures.

Lõplikud järeldused:

kui teil on vaja seda teha LIITU mitme korduva kirjega - parem on kasutada tabeli "sõnastikku".

kui teie sõnastikku oodatakse väike ja te ei loe sellest palju välja - võite kasutada json[b]

kõigil muudel juhtudel hstore + array_agg(i::text) saab olema tõhusam

Allikas: www.habr.com

PostgreSQL-i antimustrid: lööme sõnastikuga raske JOIN

Piiratud sõnavaras palju "fakte".

hstore-sõnastik

Tabelikirjed: serialiseerimine ja deserialiseerimine

jsoni sõnastik

Toimivuse testimine

Lisa kommentaar Tühista vastus