ProHoster > blog > administratie > PostgreSQL Antipatterns: laten we de zware JOIN aangaan met een woordenboek

PostgreSQL Antipatterns: laten we de zware JOIN aangaan met een woordenboek

We vervolgen de reeks artikelen gewijd aan de studie van weinig bekende manieren om de prestaties van “schijnbaar eenvoudige” PostgreSQL-query’s te verbeteren:

Denk maar niet dat ik JOIN niet zo leuk vind... :)

Maar vaak blijkt het verzoek zonder dit aanzienlijk productiever te zijn dan ermee. Dus vandaag gaan we het proberen ontdoen van resource-intensieve JOIN - gebruik van een woordenboek.

PostgreSQL Antipatterns: laten we de zware JOIN aangaan met een woordenboek

Vanaf PostgreSQL 12 kunnen sommige van de hieronder beschreven situaties enigszins anders worden gereproduceerd vanwege standaard niet-materialisatie CTE. Dit gedrag kan worden teruggedraaid door de sleutel op te geven MATERIALIZED.

Veel ‘feiten’ in een beperkte woordenschat

Laten we een heel reële applicatietaak nemen: we moeten een lijst weergeven binnenkomende berichten of actieve taken met afzenders:

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

In de abstracte wereld zouden taakauteurs gelijkmatig verdeeld moeten zijn over alle medewerkers van onze organisatie, maar in werkelijkheid taken komen in de regel van een vrij beperkt aantal mensen - “van het management” hogerop in de hiërarchie of “van onderaannemers” van aangrenzende afdelingen (analisten, ontwerpers, marketing, ...).

Laten we accepteren dat in onze organisatie van 1000 mensen slechts 20 auteurs (meestal zelfs minder) taken opleggen voor elke specifieke uitvoerder en Laten we deze vakkennis gebruikenom de "traditionele" vraag te versnellen.

Scriptgenerator

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

Laten we de laatste 100 taken voor een specifieke uitvoerder weergeven:

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;

PostgreSQL Antipatterns: laten we de zware JOIN aangaan met een woordenboek
[kijk naar explain.tensor.ru]

Het blijkt dat 1/3 totale tijd en 3/4 metingen Er werden alleen pagina's met gegevens gemaakt om 100 keer naar de auteur te zoeken - voor elke uitvoertaak. Maar dat weten we onder deze honderden slechts 20 verschillende - Is het mogelijk om deze kennis te gebruiken?

hstore-woordenboek

Laten we profiteren hwinkeltype om een ​​sleutelwaarde voor een woordenboek te genereren:

CREATE EXTENSION hstore

We hoeven alleen maar de ID van de auteur en zijn naam in het woordenboek te zetten, zodat we het vervolgens met deze sleutel kunnen extraheren:

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

PostgreSQL Antipatterns: laten we de zware JOIN aangaan met een woordenboek
[kijk naar explain.tensor.ru]

Besteed aan het verkrijgen van informatie over personen 2 keer minder tijd en 7 keer minder gegevens lezen! Wat ons naast de “woordenschat” ook heeft geholpen deze resultaten te bereiken, was ophalen van bulkrecords van de tafel in één keer met behulp van = ANY(ARRAY(...)).

Tabelgegevens: serialisatie en deserialisatie

Maar wat als we niet slechts één tekstveld, maar een hele vermelding in het woordenboek moeten opslaan? In dit geval zal de vaardigheid van PostgreSQL ons helpen behandel een tabelinvoer als een enkele waarde:

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

Laten we eens kijken wat hier aan de hand was:

  1. Wij namen p als een alias voor de volledige persoonstabelinvoer en verzamelde er een reeks van.
  2. deze de reeks opnames werd herschikt naar een array met tekstreeksen (person[]::text[]) om deze als een array met waarden in het hstore-woordenboek te plaatsen.
  3. Wanneer we een gerelateerd record ontvangen, zullen we met de sleutel uit het woordenboek gehaald als tekststring.
  4. We hebben tekst nodig veranderen in een tabeltypewaarde persoon (voor elke tafel wordt automatisch een type met dezelfde naam aangemaakt).
  5. “Breid” het getypte record uit in kolommen met behulp van (...).*.

json-woordenboek

Maar zo'n truc zoals we hierboven hebben toegepast, zal niet werken als er geen corresponderend tabeltype is om de "casting" uit te voeren. Precies dezelfde situatie zal zich voordoen, en als we proberen te gebruiken een CTE-rij, geen "echte" tabel.

In dit geval zullen zij ons helpen functies voor het werken met 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;

Opgemerkt moet worden dat we bij het beschrijven van de doelstructuur niet alle velden van de bronreeks kunnen vermelden, maar alleen de velden die we echt nodig hebben. Als we een “native” tabel hebben, is het beter om de functie te gebruiken json_populate_record.

We hebben nog steeds één keer toegang tot het woordenboek, maar json-[de]serialisatiekosten zijn behoorlijk hoogDaarom is het redelijk om deze methode alleen in sommige gevallen te gebruiken wanneer de “eerlijke” CTE-scan slechter blijkt.

Prestaties testen

We hebben dus twee manieren om gegevens in een woordenboek te serialiseren − hstore/json_object. Daarnaast kunnen de arrays van sleutels en waarden zelf ook op twee manieren worden gegenereerd, met interne of externe conversie naar tekst: array_agg(i::tekst) / array_agg(i)::tekst[].

Laten we de effectiviteit van verschillende soorten serialisatie controleren aan de hand van een puur synthetisch voorbeeld: serialiseer verschillende aantallen sleutels:

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

Evaluatiescript: serialisatie

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 Antipatterns: laten we de zware JOIN aangaan met een woordenboek

Op PostgreSQL 11, tot ongeveer een woordenboekgrootte van 2^12 sleutels serialisatie naar json kost minder tijd. In dit geval is de combinatie van json_object en “interne” typeconversie het meest effectief array_agg(i::text).

Laten we nu proberen de waarde van elke sleutel 8 keer te lezen - waarom is het tenslotte nodig als u geen toegang heeft tot het woordenboek?

Evaluatiescript: lezen uit een woordenboek

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;

PostgreSQL Antipatterns: laten we de zware JOIN aangaan met een woordenboek

En... al ongeveer met 2^6 sleutels begint het lezen uit een JSON-woordenboek meerdere keren te verliezen lezen vanuit hstore, voor jsonb gebeurt hetzelfde op 2^9.

Eindconclusies:

  • als je het moet doen JOIN met meerdere herhalende records — het is beter om het “woordenboek” van de tabel te gebruiken
  • als je woordenboek wordt verwacht klein en je leest er niet veel van - je kunt json[b] gebruiken
  • in alle andere gevallen hstore + array_agg(i::tekst) zal effectiever zijn

Bron: www.habr.com

Voeg een reactie