🥇PostgreSQL Antipatterns: wacha tupige JIUNGE na kamusi nzito

Tunaendeleza safu ya vifungu vinavyotolewa kwa uchunguzi wa njia zisizojulikana za kuboresha utendakazi wa hoja "zinazoonekana rahisi" za PostgreSQL:

Usifikirie kuwa sipendi JIUNGE sana... :)

Lakini mara nyingi bila hiyo, ombi linageuka kuwa na tija zaidi kuliko nayo. Kwa hivyo leo tutajaribu ondoa JIUNGE na rasilimali nyingi - kwa kutumia kamusi.

Kuanzia na PostgreSQL 12, baadhi ya hali zilizoelezwa hapo chini zinaweza kutolewa tena kwa njia tofauti kidogo kutokana na CTE chaguo-msingi isiyo ya nyenzo. Tabia hii inaweza kurejeshwa kwa kubainisha ufunguo MATERIALIZED.

"Mambo" mengi katika msamiati mdogo

Hebu tuchukue kazi halisi ya maombi - tunahitaji kuonyesha orodha ujumbe zinazoingia au kazi zinazoendelea na watumaji:

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

Katika ulimwengu wa kufikirika, waandishi wa kazi wanapaswa kusambazwa sawasawa kati ya wafanyikazi wote wa shirika letu, lakini kwa ukweli Kazi huja, kama sheria, kutoka kwa idadi ndogo ya watu - "kutoka kwa usimamizi" juu ya uongozi au "kutoka kwa wakandarasi wadogo" kutoka idara za jirani (wachambuzi, wabunifu, uuzaji, ...).

Wacha tukubali kwamba katika shirika letu la watu 1000, waandishi 20 tu (kawaida hata chini) huweka kazi kwa kila mtendaji maalum na. Hebu tumia ujuzi wa somo hiliili kuharakisha swala la "jadi".

Jenereta ya hati

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

Wacha tuonyeshe kazi 100 za mwisho za mtekelezaji maalum:

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;

[tazama kwenye explain.tensor.ru]

Ni zinageuka kuwa 1/3 jumla ya muda na usomaji 3/4 kurasa za data zilifanywa tu kutafuta mwandishi mara 100 - kwa kila kazi ya pato. Lakini tunajua kwamba kati ya mamia haya 20 tu tofauti - Je, inawezekana kutumia ujuzi huu?

hstore-kamusi

Hebu kuchukua faida aina ya hstore kutengeneza thamani kuu ya "kamusi":

CREATE EXTENSION hstore

Tunahitaji tu kuweka kitambulisho cha mwandishi na jina lake kwenye kamusi ili tuweze kutoa kwa kutumia ufunguo huu:

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

[tazama kwenye explain.tensor.ru]

Imetumika kupata habari kuhusu watu Muda kidogo mara 2 na data iliyosomwa mara 7! Mbali na "msamiati", kilichotusaidia pia kufikia matokeo haya ilikuwa urejeshaji wa rekodi nyingi kutoka kwa meza kwa kupitisha moja kwa kutumia = ANY(ARRAY(...)).

Maingizo ya Jedwali: Kusasisha na Kuondoa bidhaa

Lakini vipi ikiwa tunahitaji kuhifadhi sio sehemu moja tu ya maandishi, lakini ingizo zima katika kamusi? Katika kesi hii, uwezo wa PostgreSQL utatusaidia chukulia ingizo la jedwali kama thamani moja:

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

Hebu tuangalie kilichokuwa kikiendelea hapa:

Tulichukua p kama lakabu kwa ingizo la jedwali la mtu kamili na akakusanya safu yao.
Hii safu ya rekodi ilionyeshwa tena kwa safu ya mifuatano ya maandishi (mtu[]::text[]) ili kuiweka katika kamusi ya hstore kama safu ya thamani.
Tunapopokea rekodi inayohusiana, sisi vunjwa kutoka kwa kamusi kwa ufunguo kama safu ya maandishi.
Tunahitaji maandishi geuza kuwa thamani ya aina ya jedwali mtu (kwa kila jedwali aina ya jina moja huundwa kiatomati).
"Panua" rekodi iliyochapwa kwenye safu wima kwa kutumia (...).*.

json kamusi

Lakini hila kama tulivyotumia hapo juu haitafanya kazi ikiwa hakuna aina ya jedwali inayolingana ya kufanya "kutupwa". Hasa hali hiyo itatokea, na ikiwa tunajaribu kutumia safu ya CTE, sio jedwali "halisi"..

Katika kesi hii watatusaidia kazi za kufanya kazi na 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;

Ikumbukwe kwamba wakati wa kuelezea muundo wa lengo, hatuwezi kuorodhesha nyanja zote za kamba ya chanzo, lakini ni zile tu ambazo tunahitaji sana. Ikiwa tuna meza ya "asili", basi ni bora kutumia kazi json_populate_record.

Bado tunapata kamusi mara moja, lakini json-[de]gharama za usanifu ni kubwa sana, kwa hiyo, ni busara kutumia njia hii tu katika baadhi ya matukio wakati "waaminifu" CTE Scan inajionyesha kuwa mbaya zaidi.

Utendaji wa kupima

Kwa hivyo, tulipata njia mbili za kusawazisha data katika kamusi - hstore/json_object. Kwa kuongezea, safu za funguo na maadili zenyewe zinaweza pia kuzalishwa kwa njia mbili, na ubadilishaji wa ndani au wa nje kuwa maandishi: array_agg(i::text) / array_agg(i)::text[].

Wacha tuangalie ufanisi wa aina tofauti za usanifu kwa kutumia mfano wa syntetisk - kusasisha nambari tofauti za funguo:

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

Hati ya tathmini: utayarishaji

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;

Kwenye PostgreSQL 11, hadi takriban saizi ya kamusi ya vitufe 2^12 kusasisha kwa json inachukua muda kidogo. Katika kesi hii, ufanisi zaidi ni mchanganyiko wa json_object na uongofu wa aina ya "ndani". array_agg(i::text).

Sasa hebu jaribu kusoma thamani ya kila ufunguo mara 8 - baada ya yote, ikiwa huna kufikia kamusi, basi kwa nini inahitajika?

Hati ya tathmini: kusoma kutoka kwa kamusi

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;

Na ... tayari takriban na vitufe 2^6, kusoma kutoka kwa kamusi ya json huanza kupoteza mara nyingi kusoma kutoka hstore, kwa jsonb hiyo hiyo hufanyika kwa 2^9.

Hitimisho la mwisho:

ikiwa unahitaji kuifanya JIUNGE na rekodi nyingi zinazojirudia - ni bora kutumia "kamusi" ya meza

ikiwa kamusi yako inatarajiwa ndogo na hautasoma mengi kutoka kwayo - unaweza kutumia json[b]

katika kesi nyingine zote hstore + array_agg(i:: maandishi) itakuwa na ufanisi zaidi

Chanzo: mapenzi.com

PostgreSQL Antipatterns: wacha tupige JOIN nzito na kamusi

"Mambo" mengi katika msamiati mdogo

hstore-kamusi

Maingizo ya Jedwali: Kusasisha na Kuondoa bidhaa

json kamusi

Utendaji wa kupima

Kuongeza maoni Kufuta kujibu