🥇PostgreSQL-teenpatrone: kom ons slaan die swaar JOIN met die woordeboek

Ons gaan voort met die reeks artikels wat gewy is aan die studie van min bekende maniere om die werkverrigting van "oënskynlik eenvoudige" PostgreSQL-navrae te verbeter:

Moenie dink dat ek nie so baie van JOIN hou nie... :)

Maar dikwels daarsonder blyk die versoek aansienlik meer produktief te wees as daarmee. So vandag gaan ons probeer ontslae te raak van hulpbron-intensiewe JOIN - die gebruik van 'n woordeboek.

Begin met PostgreSQL 12, kan sommige van die situasies wat hieronder beskryf word effens anders gereproduseer word a.g.v. verstek nie-materialisering CTE. Hierdie gedrag kan teruggekeer word deur die sleutel te spesifiseer MATERIALIZED.

Baie "feite" in 'n beperkte woordeskat

Kom ons neem 'n baie werklike toepassingstaak - ons moet 'n lys vertoon inkomende boodskappe of aktiewe take met senders:

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

In die abstrakte wêreld moet taakskrywers eweredig onder alle werknemers van ons organisasie versprei word, maar in werklikheid take kom, as 'n reël, van 'n redelik beperkte aantal mense - "van bestuur" op die hiërargie of "van subkontrakteurs" van naburige departemente (ontleders, ontwerpers, bemarking, ...).

Kom ons aanvaar dat in ons organisasie van 1000 mense, slegs 20 skrywers (gewoonlik selfs minder) take vir elke spesifieke kunstenaar en Kom ons gebruik hierdie vakkennisom die "tradisionele" navraag te bespoedig.

Skripgenerator

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

Kom ons wys die laaste 100 take vir 'n spesifieke eksekuteur:

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;

[kyk na explain.tensor.ru]

Dit blyk dat 1/3 totale tyd en 3/4 lesings bladsye met data is slegs gemaak om 100 keer na die skrywer te soek - vir elke uitvoertaak. Maar ons weet dit onder hierdie honderde net 20 verskillende - Is dit moontlik om hierdie kennis te gebruik?

hstore-woordeboek

Kom ons trek voordeel uit hstore tipe om 'n "woordeboek" sleutelwaarde te genereer:

CREATE EXTENSION hstore

Ons hoef net die outeur se ID en sy naam in die woordeboek te plaas sodat ons dan kan onttrek met hierdie sleutel:

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

[kyk na explain.tensor.ru]

Bestee aan die verkryging van inligting oor persone 2 keer minder tyd en 7 keer minder data gelees! Benewens "woordeskat", was wat ons ook gehelp het om hierdie resultate te bereik grootmaat rekord herwinning van die tafel in 'n enkele pas met behulp van = ANY(ARRAY(...)).

Tabelinskrywings: Serialisering en deserialisering

Maar wat as ons nie net een teksveld moet stoor nie, maar 'n hele inskrywing in die woordeboek? In hierdie geval sal PostgreSQL se vermoë ons help behandel 'n tabelinskrywing as 'n 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;

Kom ons kyk wat hier aan die gang was:

Ons het geneem p as 'n alias vir die volle persoon-tabelinskrywing en 'n menigte van hulle bymekaar gemaak.
Dit die reeks opnames is hersaamgestel na 'n verskeidenheid teksstringe (persoon[]::teks[]) om dit in die hstore-woordeboek te plaas as 'n verskeidenheid waardes.
Wanneer ons 'n verwante rekord ontvang, sal ons per sleutel uit die woordeboek gehaal as 'n teksstring.
Ons het teks nodig verander in 'n tabeltipe waarde persoon (vir elke tabel word 'n tipe met dieselfde naam outomaties geskep).
"Brei" die getikte rekord in kolomme met behulp van (...).*.

json woordeboek

Maar so 'n truuk soos ons hierbo toegepas het, sal nie werk as daar nie 'n ooreenstemmende tabeltipe is om die "casting" te doen nie. Presies dieselfde situasie sal ontstaan, en as ons probeer om te gebruik 'n CTE-ry, nie 'n "regte" tabel nie.

In hierdie geval sal hulle ons help funksies om met json te werk:

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

Daar moet kennis geneem word dat wanneer ons die teikenstruktuur beskryf, ons nie al die velde van die bronstring kan lys nie, maar slegs dié wat ons regtig nodig het. As ons 'n "inheemse" tabel het, is dit beter om die funksie te gebruik json_populate_record.

Ons kry nog een keer toegang tot die woordeboek, maar json-[de]serialiseringskoste is redelik hoog, daarom is dit redelik om hierdie metode slegs in sommige gevalle te gebruik wanneer die "eerlike" CTE Scan homself erger toon.

Toets prestasie

So, ons het twee maniere om data in 'n woordeboek te serialiseer - hstore/json_object. Daarbenewens kan die skikkings van sleutels en waardes self ook op twee maniere gegenereer word, met interne of eksterne omskakeling na teks: array_agg(i::text) / array_agg(i)::text[].

Kom ons kyk na die doeltreffendheid van verskillende tipes serialisering deur 'n suiwer sintetiese voorbeeld te gebruik - reeks verskillende nommers sleutels:

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

Evalueringsskrif: serialisering

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, tot ongeveer 'n woordeboekgrootte van 2^12 sleutels serialisering na json neem minder tyd. In hierdie geval is die mees effektiewe kombinasie van json_object en "interne" tipe omskakeling array_agg(i::text).

Kom ons probeer nou om die waarde van elke sleutel 8 keer te lees - immers, as jy nie toegang tot die woordeboek het nie, hoekom is dit dan nodig?

Evalueringsskrif: lees uit 'n woordeboek

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 ... reeds ongeveer met 2^6 sleutels begin lees uit 'n json-woordeboek verskeie kere verloor lees vanaf hstore, vir jsonb gebeur dieselfde by 2^9.

Finale gevolgtrekkings:

as jy dit moet doen SLUIT aan met verskeie herhalende rekords - dit is beter om "woordeboek" van die tabel te gebruik

as jou woordeboek verwag word klein en jy sal nie veel daaruit lees nie - jy kan json[b] gebruik

in alle ander gevalle hstore + array_agg(i::text) meer effektief sal wees

Bron: will.com

PostgreSQL Antipatterns: Woordeboek Tref Heavy JOIN

Baie "feite" in 'n beperkte woordeskat

hstore-woordeboek

Tabelinskrywings: Serialisering en deserialisering

json woordeboek

Toets prestasie

Voeg 'n opmerking Kanselleer antwoord