🥇PostgreSQL антипаттерндері: сөздікпен ауыр JOIN-ді жеңейік

Біз «қарапайым болып көрінетін» PostgreSQL сұрауларының өнімділігін жақсартудың аз белгілі жолдарын зерттеуге арналған мақалалар сериясын жалғастырамыз:

Маған JOIN ұнамайды деп ойламаңыз... :)

Бірақ көбінесе онсыз сұрау оған қарағанда әлдеқайда өнімді болып шығады. Сондықтан бүгін біз тырысамыз ресурстарды көп қажет ететін JOIN-ден құтылыңыз - сөздікті пайдалану.

PostgreSQL 12-ден бастап, төменде сипатталған кейбір жағдайлар мыналарға байланысты аздап басқаша шығарылуы мүмкін. әдепкі материалданбаған CTE. Бұл әрекетті кілтті көрсету арқылы қайтаруға болады MATERIALIZED.

Шектеулі сөздікте көптеген «фактілер».

Өте нақты қолданбалы тапсырманы алайық - тізімді көрсету керек кіріс хабарлар немесе жіберушілермен белсенді тапсырмалар:

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

Абстрактілі әлемде тапсырма авторлары ұйымымыздың барлық қызметкерлері арасында біркелкі бөлінуі керек, бірақ шын мәнінде міндеттер, әдетте, жеткілікті шектеулі адамдардан келеді - иерархия бойынша «басшылықтан» немесе көрші бөлімдерден (талдаушылар, дизайнерлер, маркетинг, ...) «қосалқы мердігерлерден».

Біздің 1000 адамнан тұратын ұйымда тек 20 автор (әдетте одан да аз) әрбір нақты орындаушыға міндеттер қоятынын және Осы пән бойынша алған білімімізді қолданайық«дәстүрлі» сұрауды жылдамдату үшін.

Сценарий генераторы

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

Нақты орындаушы үшін соңғы 100 тапсырманы көрсетейік:

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;

[express.tensor.ru сайтынан қарау]

Көрсетіледі 1/3 жалпы уақыт және 3/4 оқу деректер беттері авторды 100 рет іздеу үшін ғана жасалды - әрбір шығыс тапсырмасы үшін. Бірақ біз бұл жүздегеннің ішінде екенін білеміз тек 20 түрлі - Бұл білімді пайдалануға болады ма?

hstore-сөздік

Пайдаланайық hstore түрі «сөздік» кілт-мәнін жасау үшін:

CREATE EXTENSION hstore

Бізге автордың идентификаторы мен оның атын сөздікке енгізу керек, сонда біз осы кілтті пайдаланып шығарып аламыз:

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

[express.tensor.ru сайтынан қарау]

тұлғалар туралы ақпарат алуға жұмсалған 2 есе аз уақыт және 7 есе аз деректерді оқу! «Сөздік қордан» басқа, бізге бұл нәтижелерге жетуге көмектескен нәрсе болды жазбаларды жаппай алу кестеден бір өту арқылы = ANY(ARRAY(...)).

Кесте жазбалары: сериялау және сериядан шығару

Бірақ сөздікте бір ғана мәтіндік өрісті емес, бүкіл жазбаны сақтау қажет болса ше? Бұл жағдайда PostgreSQL мүмкіндігі бізге көмектеседі кесте жазбасын жалғыз мән ретінде қарастырыңыз:

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

Мұнда не болып жатқанын қарастырайық:

Біз алдық p толық тұлға кестесі жазбасына бүркеншік ат ретінде және олардың массивін жинады.
осы жазбалар массиві қайта өңделді мәндер жиымы ретінде hstore сөздігіне орналастыру үшін мәтін жолдарының массивіне (person[]::text[]) таңдаңыз.
Тиісті жазбаны алған кезде біз перне арқылы сөздіктен алынған мәтіндік жол ретінде.
Бізге мәтін керек кесте түрінің мәніне айналдырыңыз адам (әр кесте үшін аттас түрі автоматты түрде жасалады).
көмегімен терілген жазбаны бағандарға «кеңейту». (...).*.

json сөздігі

Бірақ біз жоғарыда қолданған трюк, егер «кастинг» жасау үшін сәйкес кесте түрі болмаса, жұмыс істемейді. Дәл осындай жағдай туындайды, егер біз қолданып көрсек «нақты» кесте емес, CTE жолы.

Бұл жағдайда олар бізге көмектеседі 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;

Айта кету керек, мақсатты құрылымды сипаттау кезінде біз бастапқы жолдың барлық өрістерін тізімдей алмаймыз, тек бізге шынымен қажет. Егер бізде «туған» кесте болса, функцияны қолданған дұрыс json_populate_record.

Біз әлі де сөздікке бір рет кіреміз, бірақ json-[de]сериализация шығындары өте жоғары, сондықтан бұл әдісті кейбір жағдайларда ғана «адал» CTE сканерлеуі нашарлағанда қолдану орынды.

Тестілеу өнімділігі

Сонымен, бізде деректерді сөздікке сериялаудың екі жолы бар - hstore/json_object. Сонымен қатар, кілттер мен мәндердің массивтерін мәтінге ішкі немесе сыртқы түрлендіру арқылы екі жолмен жасауға болады: array_agg(i::text) / array_agg(i)::text[].

Таза синтетикалық мысалды пайдалана отырып, сериялаудың әртүрлі түрлерінің тиімділігін тексерейік - кілттердің әртүрлі сандарын сериялау:

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

Бағалау сценарийі: сериялау

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 жүйесінде сөздік өлшемі шамамен 2^12 кілтке дейін json жүйесіне сериялау аз уақытты алады. Бұл жағдайда json_object және «ішкі» түр түрлендіру комбинациясы ең тиімді болып табылады array_agg(i::text).

Енді әрбір перненің мәнін 8 рет оқып көрейік - ақыр соңында, егер сіз сөздікке қол жеткізе алмасаңыз, онда ол не үшін қажет?

Бағалау сценарийі: сөздіктен оқу

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;

Ал... шамамен 2^6 пернелерімен json сөздігінен оқу бірнеше рет жоғала бастайды hstore-дан оқу, jsonb үшін 2^9-да бірдей болады.

Қорытынды қорытындылар:

істеу керек болса Бірнеше қайталанатын жазбалармен ҚОСЫЛЫҢЫЗ — кестенің «сөздігін» қолданған дұрыс

егер сіздің сөздігіңіз күтілсе кішкентай және сіз одан көп оқи алмайсыз - json[b] пайдалана аласыз

барлық басқа жағдайларда hstore + array_agg(i::text) тиімдірек болады

Ақпарат көзі: www.habr.com

PostgreSQL антипаттерндері: сөздікпен ауыр JOIN-ге жетейік

Шектеулі сөздікте көптеген «фактілер».

hstore-сөздік

Кесте жазбалары: сериялау және сериядан шығару

json сөздігі

Тестілеу өнімділігі

пікір қалдыру Жауапты болдырмау