PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

Jatkamme artikkelisarjaa, joka on omistettu tutkimaan vähän tunnettuja tapoja parantaa "näennäisesti yksinkertaisten" PostgreSQL-kyselyiden suorituskykyä:

• harvinainen ennätys saavuttaa JOIN:n puolivälin
• Sisyphean JOIN-taulukot
• haitallinen JOIN ja OR
• CTE LIITY CTE:hen

Älä luule, että en pidä JOINista niin paljon... :)

Mutta usein ilman sitä pyyntö osoittautuu huomattavasti tuottavammaksi kuin sen kanssa. Joten tänään yritetään päästä eroon resurssiintensiivisestä JOINista - sanakirjan käyttö.

PostgreSQL 12:sta alkaen jotkin alla kuvatuista tilanteista voivat toistua hieman eri tavalla johtuen oletuksena ei-materialisoituminen CTE. Tämä toiminta voidaan peruuttaa määrittämällä avain MATERIALIZED.

Paljon "faktoja" rajoitetussa sanastossa

Otetaanpa hyvin todellinen sovellustehtävä - meidän on näytettävä luettelo saapuvat viestit tai aktiiviset tehtävät lähettäjien kanssa:

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

Abstraktissa maailmassa tehtävien kirjoittajat tulisi jakaa tasaisesti kaikkien organisaatiomme työntekijöiden kesken, mutta todellisuudessa Tehtävät tulevat pääsääntöisesti melko rajatulta määrältä ihmisiä - "johdolta" ylöspäin hierarkiassa tai "alihankkijilta" naapuriosastoilta (analyytikot, suunnittelijat, markkinointi jne.).

Hyväksytään, että 1000 hengen organisaatiossamme vain 20 kirjoittajaa (yleensä jopa vähemmän) asettaa tehtäviä kullekin tietylle esiintyjälle ja Käytetään tätä aihetietoa"perinteisen" kyselyn nopeuttamiseksi.

Script generaattori

-- сотрудники
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äytämme tietyn suorittajan viimeiset 100 tehtävää:

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;

[katso selittää.tensor.ru]

On käynyt ilmi, että 1/3 kokonaisajasta ja 3/4 lukemia sivuja tietoja tehtiin vain etsimään tekijää 100 kertaa - jokaista tulostehtävää kohti. Mutta tiedämme sen näiden satojen joukossa vain 20 erilaista - Onko tätä tietoa mahdollista käyttää?

hstore-sanakirja

Hyödynnetään hstore tyyppi "sanakirjan" avainarvon luominen:

CREATE EXTENSION hstore

Meidän on vain lisättävä tekijän tunnus ja hänen nimensä sanakirjaan, jotta voimme poimia sen käyttämällä tätä avainta:

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

[katso selittää.tensor.ru]

Käytettiin henkilötietojen hankkimiseen 2 kertaa vähemmän aikaa ja 7 kertaa vähemmän dataa! "Sanaston" lisäksi myös se, mikä auttoi meitä saavuttamaan nämä tulokset, oli joukkotietueiden haku pöydältä yhdellä siirrolla käyttämällä = ANY(ARRAY(...)).

Taulukkomerkinnät: Serialization ja Deserialization

Mutta entä jos meidän ei tarvitse tallentaa vain yhtä tekstikenttää, vaan koko sanakirjaan? Tässä tapauksessa PostgreSQL:n kyky auttaa meitä käsitellä taulukkomerkintää yhtenä arvona:

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

Katsotaanpa mitä täällä tapahtuu:

1. Otimme p koko henkilötaulukon merkinnän aliaksena ja kokosi niistä joukon.
2. tämä nauhoitussarja laadittiin uudelleen tekstimerkkijonoihin (person[]::text[]) sijoittaaksesi sen hstore-sanakirjaan arvojen joukkona.
3. Kun saamme asiaan liittyvän tietueen, me haettu sanakirjasta avaimella tekstijonona.
4. Tarvitsemme tekstiä muuntaa taulukkotyypin arvoksi henkilö (jokaiselle taulukolle luodaan automaattisesti samanniminen tyyppi).
5. "Laajenna" kirjoitettu tietue sarakkeiksi käyttämällä (...).*.

json-sanakirja

Mutta edellä sovellettu temppu ei toimi, jos vastaavaa taulukkotyyppiä ei ole "valua" varten. Täsmälleen sama tilanne syntyy, ja jos yritämme käyttää CTE-rivi, ei "oikea" taulukko.

Tässä tapauksessa he auttavat meitä toimintoja jsonin kanssa työskentelyyn:

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

On huomattava, että kohderakennetta kuvattaessa emme voi luetella kaikkia lähdemerkkijonon kenttiä, vaan vain niitä, joita todella tarvitsemme. Jos meillä on "natiivi" taulukko, on parempi käyttää funktiota json_populate_record.

Käytämme edelleen sanakirjaa kerran, mutta json-[de]serialisointikustannukset ovat melko korkeatSiksi on järkevää käyttää tätä menetelmää vain joissakin tapauksissa, kun "rehellinen" CTE Scan näyttää olevansa huonompi.

Testaa suorituskykyä

Joten meillä on kaksi tapaa sarjoida tiedot sanakirjaan − hstore/json_object. Lisäksi itse avainten ja arvojen taulukot voidaan luoda kahdella tavalla, sisäisellä tai ulkoisella muuntamalla tekstiksi: array_agg(i::teksti) / array_agg(i)::teksti[].

Tarkastellaan erityyppisten sarjoitusten tehokkuutta puhtaasti synteettisellä esimerkillä - sarjaa eri numerot avaimia:

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

Arviointiskripti: serialisointi

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:ssä enintään noin 2^12 avaimen sanakirjakoko serialisointi jsoniin vie vähemmän aikaa. Tässä tapauksessa tehokkain on json_object- ja "sisäisen" tyyppimuunnoksen yhdistelmä array_agg(i::text).

Yritetään nyt lukea kunkin avaimen arvo 8 kertaa - jos et kuitenkaan käytä sanakirjaa, miksi sitä tarvitaan?

Arviointiskripti: lukeminen sanakirjasta

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... jo suunnilleen 2^6 näppäimellä lukeminen json-sanakirjasta alkaa menettää useita kertoja lukeminen hstoresta, jsonb:lle sama tapahtuu kohdassa 2^9.

Lopulliset johtopäätökset:

• jos sinun tarvitsee tehdä se LIITY useilla toistuvilla tietueilla - on parempi käyttää taulukon "sanakirjaa".
• jos sanakirjaasi odotetaan pieni, etkä lue siitä paljon - voit käyttää json[b]
• kaikissa muissa tapauksissa hstore + array_agg(i::teksti) tulee olemaan tehokkaampi

Lähde: will.com