🥇PostgreSQL Antipatterns: litte wy de swiere JOIN slaan mei it wurdboek

Wy geane troch mei de searje artikels wijd oan 'e stúdzje fan min bekende manieren om de prestaasjes fan "skynber ienfâldige" PostgreSQL-fragen te ferbetterjen:

Tink net dat ik JOIN net sa leuk fyn ... :)

Mar faaks sûnder it, it fersyk blykt te wêzen oanmerklik produktiver as mei it. Dus hjoed sille wy besykje get rid of boarne-yntinsive JOIN - it brûken fan in wurdboek.

Begjinnend mei PostgreSQL 12 kinne guon fan 'e hjirûnder beskreaune situaasjes wat oars wurde reprodusearre fanwegen standert net-materialisaasje CTE. Dit gedrach kin weromdraaid wurde troch de kaai op te jaan MATERIALIZED.

In protte "feiten" yn in beheinde wurdskat

Litte wy in heul echte tapassingstaak nimme - wy moatte in list werjaan ynkommende berjochten of aktive taken mei stjoerders:

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

Yn 'e abstrakte wrâld moatte taakauteurs lykwichtich ferdield wurde ûnder alle meiwurkers fan ús organisaasje, mar yn werklikheid taken komme, yn 'e regel, fan in frij beheind tal minsken - "fan management" de hiërargy omheech of "fan subcontractors" fan oanbuorjende ôfdielingen (analisten, ûntwerpers, marketing, ...).

Litte wy akseptearje dat yn ús organisaasje fan 1000 minsken mar 20 auteurs (meastentiids noch minder) taken foar elke spesifike performer en Litte wy dizze fakkennis brûkeom de "tradisjonele" query te fersnellen.

Skript generator

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

Litte wy de lêste 100 taken foar in spesifike útfierer sjen litte:

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;

[sjoch op explain.tensor.ru]

It bliuwt dat 1/3 totale tiid en 3/4 lêzingen siden mei gegevens waarden allinich makke om de auteur 100 kear te sykjen - foar elke útfiertaak. Mar wy witte dat ûnder dizze hûnderten allinne 20 ferskillende - Is it mooglik om dizze kennis te brûken?

hstore-wurdboek

Lit ús profitearje hstore type om in "wurdboek" kaai-wearde te generearjen:

CREATE EXTENSION hstore

Wy moatte gewoan de ID fan de auteur en syn namme yn it wurdboek pleatse, sadat wy dan kinne ekstrahearje mei dizze kaai:

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

[sjoch op explain.tensor.ru]

Besteegje oan it krijen fan ynformaasje oer persoanen 2 kear minder tiid en 7 kear minder gegevens lêzen! Neist "wurdskat" wie wat ús ek holp om dizze resultaten te berikken bulk record opheljen út 'e tafel yn in inkele pass brûkend = ANY(ARRAY(...)).

Tabel Entries: Serialization en Deserialization

Mar wat as wy net allinich ien tekstfjild moatte bewarje, mar in hiele yngong yn it wurdboek? Yn dit gefal sil it fermogen fan PostgreSQL ús helpe behannelje in tabel yngong as ien wearde:

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

Litte wy sjen nei wat hjir barde:

Wy namen p as in alias foar de folsleine persoan tabel yngong en sammele in rige fan harren.
dizze de array fan opnames waard omfoarme nei in array fan tekststrings (persoan[]::tekst[]) om it yn it hstore-wurdboek te pleatsen as in array fan wearden.
As wy ûntfange in besibbe rekord, wy mei de kaai út it wurdboek helle as in tekststring.
Wy moatte tekst feroarje yn in tabel type wearde persoan (foar elke tabel wurdt automatysk in type mei deselde namme oanmakke).
"Wreidzje" it typte record út yn kolommen mei (...).*.

json wurdboek

Mar sa'n trúk lykas wy hjirboppe tapast, sil net wurkje as d'r gjin oerienkommende tabeltype is om de "casting" te dwaan. Krekt deselde situaasje sil ûntstean, en as wy besykje te brûken in CTE rige, net in "echte" tabel.

Yn dit gefal sille se ús helpe funksjes foar wurkjen mei 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;

Dêrby moat opmurken wurde dat by it beskriuwen fan de doelstruktuer, wy kinne net list alle fjilden fan de boarne string, mar allinnich dejingen dy't wy echt nedich. As wy in "native" tabel hawwe, dan is it better om de funksje te brûken json_populate_record.

Wy krije noch ien kear tagong ta it wurdboek, mar json-[de]serialisaasjekosten binne frij heech, dêrom is it ridlik om dizze metoade allinich yn guon gefallen te brûken as de "earlike" CTE Scan himsels slimmer toant.

Testing prestaasjes

Dat, wy hawwe twa manieren om gegevens te serialisearjen yn in wurdboek - hstore/json_object. Derneist kinne de arrays fan kaaien en wearden sels ek op twa manieren generearre wurde, mei ynterne as eksterne konverzje nei tekst: array_agg(i::tekst) / array_agg(i)::tekst[].

Litte wy de effektiviteit fan ferskate soarten serialisaasje kontrolearje mei in suver syntetysk foarbyld - serialisearje ferskillende oantallen kaaien:

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

Evaluaasje skript: serialization

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;

Op PostgreSQL 11, oant likernôch in wurdboekgrutte fan 2^12 kaaien serialisaasje nei json nimt minder tiid. Yn dit gefal is it meast effektyf de kombinaasje fan json_object en "ynterne" type konverzje array_agg(i::text).

Litte wy no besykje de wearde fan elke kaai 8 kear te lêzen - ommers, as jo gjin tagong krije ta it wurdboek, wêrom is it dan nedich?

Evaluaasjeskript: lêzen út in wurdboek

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;

En... al sawat mei 2 ^ 6 toetsen begjint it lêzen fan in json wurdboek meardere kearen te ferliezen lêzen fan hstore, foar jsonb bart itselde by 2 ^ 9.

Finale konklúzjes:

as jo it moatte dwaan JOIN mei meardere werheljende records - it is better om "wurdboek" fan 'e tabel te brûken

as jo wurdboek wurdt ferwachte lyts en do silst der net folle fan lêze - jo kinne json[b] brûke

yn alle oare gefallen hstore + array_agg(i::tekst) sil effektiver wêze

Boarne: www.habr.com

PostgreSQL Antipatterns: lit ús de swiere JOIN slaan mei in wurdboek

In protte "feiten" yn in beheinde wurdskat

hstore-wurdboek

Tabel Entries: Serialization en Deserialization

json wurdboek

Testing prestaasjes

Add a comment cancel reply