🥇PostgreSQL Antipatterns: jo dezagun astuna JOIN hiztegiarekin

"Itxuraz sinpleak" PostgreSQL kontsulten errendimendua hobetzeko modu ezezagunak aztertzeari eskainitako artikulu sortarekin jarraitzen dugu:

Ez pentsa JOIN hainbeste gustatzen ez zaidanik... :)

Baina askotan hori gabe, eskaera horrekin baino nabarmen produktiboagoa izaten da. Beraz, gaur saiatuko gara kentzeko baliabide intentsiboa JOIN - Hiztegi bat erabiliz.

PostgreSQL 12-tik hasita, behean deskribatutako egoera batzuk zertxobait ezberdin erreproduzitu daitezke, ondorioz lehenetsi gabeko materializazio CTE. Jokaera hori itzul daiteke gakoa zehaztuz MATERIALIZED.

"Gertaera" asko hiztegi mugatuan

Har dezagun aplikazio-zeregin oso erreala - zerrenda bat erakutsi behar dugu sarrerako mezuak edo zeregin aktiboak igorleekin:

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

Mundu abstraktuan, zereginen egileak uniformeki banatu behar dira gure erakundeko langile guztien artean, baina errealitatean zereginak, oro har, jende kopuru nahiko mugatu batetik datoz - «Kudeaketatik» hierarkian gora edo ondoko sailetako «azpikontratetatik» (analistak, diseinatzaileak, marketina,...).

Onar dezagun 1000 laguneko gure erakundean 20 egilek (normalean, are gutxiago) bakarrik jarduten duten interpretatzaile bakoitzari zereginak ezartzen dizkiola eta Erabili gaitezen jakintza haukontsulta "tradizionala" bizkortzeko.

Script-sorgailua

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

Erakuts ditzagun azken 100 zereginak exekutatzaile jakin baterako:

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;

[ikusi explain.tensor.ru helbidean]

Izarrekin bihurtzen da 1/3 denbora guztira eta 3/4 irakurketa datu orrialdeak egilea 100 aldiz bilatzeko bakarrik egin ziren - irteerako zeregin bakoitzerako. Baina badakigu ehunka horien artean bakarrik 20 ezberdin - Posible al da ezagutza hori erabiltzea?

hdenda-hiztegia

Aprobetxa dezagun h denda mota "Hiztegiko" gako-balioa sortzeko:

CREATE EXTENSION hstore

Hiztegian egilearen NANa eta bere izena jarri besterik ez dugu egin behar gero gako hau erabiliz ateratzeko:

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

[ikusi explain.tensor.ru helbidean]

Pertsonei buruzko informazioa eskuratzen gastatu 2 aldiz denbora gutxiago eta 7 aldiz datu gutxiago irakurri! “Hiztegiaz” gain, emaitza hauek lortzen lagundu ziguna izan zen Erregistro multzoan berreskuratzea mahaitik pase bakarrean erabiliz = ANY(ARRAY(...)).

Taularen sarrerak: serializazioa eta deserializazioa

Baina zer gertatzen da testu-eremu bat ez ezik, hiztegian sarrera osoa gorde behar badugu? Kasu honetan, PostgreSQLren gaitasunak lagunduko digu taulako sarrera bat balio bakar gisa tratatu:

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

Ikus dezagun zer gertatzen ari zen hemen:

Hartu genuen p pertsona osoko taulako sarreraren alias gisa eta horietako sorta bat muntatu zuen.
Hau grabazio sorta birmoldatu zen testu-kateen array batera (person[]::text[]) hstore hiztegian balio-matrize gisa kokatzeko.
Erlazionatutako erregistro bat jasotzen dugunean, guk giltzaz hiztegitik aterata testu-kate gisa.
Testua behar dugu taula motako balio bihurtu pertsona (taula bakoitzeko izen bereko mota bat sortzen da automatikoki).
"Hedatu" idatzitako erregistroa zutabeetan erabiliz (...).*.

json hiztegia

Baina goian aplikatu dugun trikimailu batek ez du funtzionatuko "casting" egiteko dagokion mahai motarik ez badago. Zehazki egoera bera sortuko da, eta erabiltzen saiatzen bagara CTE errenkada bat, ez "benetako" taula bat.

Kasu honetan lagunduko digute json-ekin lan egiteko funtzioak:

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

Kontuan izan behar da xede-egitura deskribatzerakoan ezin ditugula iturburu-katearen eremu guztiak zerrendatu, benetan behar ditugunak baizik. Taula "jatorrizkoa" badugu, hobe da funtzioa erabiltzea json_populate_record.

Oraindik behin sartzen gara hiztegira, baina json-[des]serializazio kostuak nahiko altuak dira, beraz, arrazoizkoa da metodo hau erabiltzea CTE Scan "zintzoa" okerragoa den kasu batzuetan soilik.

Errendimendua probatzea

Beraz, datuak hiztegi batean serializatzeko bi modu ditugu: - hstore/json_object. Horrez gain, gakoen eta balioen arrayak ere bi modutara sor daitezke, barneko edo kanpoko testu bihurtzeko: array_agg(i::testua) / array_agg(i)::testua[].

Egiaztatu dezagun seriazio mota ezberdinen eraginkortasuna adibide sintetiko hutsa erabiliz - gako-kopuru desberdinak serializatzea:

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

Ebaluazio-gidoia: serializazioa

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-n, gutxi gorabehera 2^12 teklako hiztegi-tamaina izan daiteke Json-erako serializazioak denbora gutxiago behar du. Kasu honetan, eraginkorrena json_object eta "barne" motako bihurketa konbinazioa da array_agg(i::text).

Orain saia gaitezen gako bakoitzaren balioa 8 aldiz irakurtzen - azken finean, hiztegira sartzen ez bazara, zergatik behar da?

Ebaluazio-gidoia: hiztegi batetik irakurtzea

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;

Eta... dagoeneko gutxi gorabehera 2^6 teklarekin, json hiztegi batetik irakurtzea hainbat aldiz galtzen hasten da hstore-tik irakurtzen, jsonb-rako gauza bera gertatzen da 2^9-n.

Azken ondorioak:

egin behar baduzu ERKARTU errepikatzen diren hainbat erregistrorekin — hobe da taulako “hiztegia” erabiltzea

zure hiztegia espero bada txikia eta ez duzu handik irakurriko - json[b] erabil dezakezu

beste kasu guztietan hstore + array_agg(i::testua) eraginkorragoa izango da

Iturria: www.habr.com

PostgreSQL Antipatterns: sakatu dezagun JOIN astuna hiztegi batekin

"Gertaera" asko hiztegi mugatuan

hdenda-hiztegia

Taularen sarrerak: serializazioa eta deserializazioa

json hiztegia

Errendimendua probatzea

Gehitu iruzkin berria Idatzi