ProHoster > Blog > Tsamaiso > PostgreSQL Antipatterns: ha re otle JOIN e boima ka bukantswe

PostgreSQL Antipatterns: ha re otle JOIN e boima ka bukantswe

Re tsoela pele ka letoto la lingoloa tse fanoeng boithutong ba mekhoa e sa tsejoeng haholo ea ho ntlafatsa ts'ebetso ea lipotso tse "bonahalang li le bonolo" tsa PostgreSQL:

Se ke oa nahana hore ha ke rate JOIN haholo ... :)

Empa hangata ntle le eona, kopo e bonahala e sebetsa haholo ho feta ka eona. Kahoo kajeno re tla leka tlosa ts'ebeliso e matla ea lisebelisoa - ho sebedisa bukantswe.

PostgreSQL Antipatterns: ha re otle JOIN e boima ka bukantswe

Ho qala ka PostgreSQL 12, a mang a maemo a hlalositsoeng ka tlase a ka hlahisoa hape ka tsela e fapaneng ka lebaka la CTE e sa sebetseng ea kamehla. Boitšoaro bona bo ka khutlisoa ka ho totobatsa senotlolo MATERIALIZED.

"Lintlha" tse ngata ka pokello ea mantsoe e fokolang

Ha re nkeng mosebetsi oa sebele oa kopo - re hloka ho hlahisa lenane melaetsa e kenang kapa mesebetsi e sebetsang le baromeli:

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

Lefatšeng le sa utloahaleng, bangoli ba mesebetsi ba lokela ho ajoa ka ho lekana har'a basebetsi bohle ba mokhatlo oa rona, empa bonneteng mesebetsi e tla, joalo ka molao, ho tsoa ho palo e fokolang ea batho - "ho tloha ho botsamaisi" ho ea holimo kapa "ho tsoa ho likonteraka tse nyane" ho tsoa mafapheng a boahelani (bahlahlobisisi, baqapi, ba mebaraka, ...).

Ha re amoheleng hore mokhatlong oa rona oa batho ba 1000, ke bangoli ba 20 feela (hangata le ka tlase ho moo) ba behang mesebetsi bakeng sa sebapali se seng le se seng. Ha re sebeliseng tsebo ea taba enaho potlakisa potso ea "setso".

Jenereithara ea mongolo

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

Ha re bontše mesebetsi e 100 ea ho qetela bakeng sa mophethahatsi ea itseng:

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: ha re otle JOIN e boima ka bukantswe
[sheba ho explain.tensor.ru]

E hlaha joalo 1/3 kakaretso ea nako le ho baloa ha 3/4 maqephe a data a entsoe feela ho batla mongoli makhetlo a 100 - bakeng sa mosebetsi o mong le o mong oa tlhahiso. Empa rea ​​tseba hore hara makholo ana tse 20 feela tse fapaneng - Na hoa khoneha ho sebelisa tsebo ee?

hstore-dictionary

Ha re nke monyetla mofuta oa hstore ho hlahisa "dictionary" bohlokoa ba bohlokoa:

CREATE EXTENSION hstore

Re hloka feela ho kenya ID ea mongoli le lebitso la hae ka har'a bukantswe e le hore re ka ntsha ka ho sebedisa senotlolo sena:

-- формируем целевую выборку
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: ha re otle JOIN e boima ka bukantswe
[sheba ho explain.tensor.ru]

E sebelisitsoe ho fumana tlhahisoleseling ka batho Nako ea 2 nako e nyane le makhetlo a 7 ho baloa ha data! Ho phaella ho "polelo ea mantsoe", se ileng sa boela sa re thusa ho finyella liphello tsena ke ho khutlisa rekoto ka bongata ho tloha tafoleng ka tsela e le 'ngoe ea ho sebelisa = ANY(ARRAY(...)).

Kenyelletso ea Lethathamo: Seriization le Deserialization

Empa ho thoe'ng haeba re hloka ho boloka eseng karolo e le 'ngoe feela ea mongolo, empa le keno eohle bukeng e hlalosang mantsoe? Tabeng ena, bokhoni ba PostgreSQL bo tla re thusa tšoara sekeno sa tafole joalo ka boleng bo le bong:

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

Ha re shebe se neng se etsahala mona:

  1. Re nkile p joalo ka lebitso la lebitso la motho ea felletseng ea kenang tafoleng mme a bokella bongata ba tsona.
  2. sena letoto la lirekoto li ile tsa fetoleloa bocha ho letoto la likhoele tsa mongolo (motho[]::text[]) ho e beha bukeng ea bukantswe ya hstore e le letoto la boleng.
  3. Ha re fumana tlaleho e amanang le eona, re ho ntšoa bukeng ka senotlolo joalo ka khoele ea mongolo.
  4. Re hloka mongolo fetola boleng ba mofuta oa tafole motho (tafoleng e 'ngoe le e 'ngoe mofuta oa lebitso le le leng o iketselitse).
  5. "Atolosa" rekoto e tlatsitsoeng ho likholomo u sebelisa (...).*.

bukantswe ya json

Empa leqheka le joalo leo re le sebelisitseng ka holimo le ke ke la sebetsa haeba ho se na mofuta oa tafole o tsamaisanang le "casting". Hantle-ntle boemo bo tšoanang bo tla hlaha, 'me haeba re leka ho sebelisa mola oa CTE, eseng tafole ea "sebele"..

Tabeng ena ba tla re thusa mesebetsi ea ho sebetsa le 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;

Hoa lokela ho hlokomeloa hore ha re hlalosa sebopeho sa sepheo, re ke ke ra thathamisa likarolo tsohle tsa mohala oa mohloli, empa ke feela tseo re li hlokang. Haeba re na le tafole ea "letsoalloa", joale ho molemo ho sebelisa mosebetsi json_populate_record.

Re ntse re fumana bukantswe hang, empa json-[de] litjeo tsa serialization li holimo haholo, ka hona, hoa utloahala ho sebelisa mokhoa ona feela maemong a mang ha CTE Scan "ea tšepahalang" e iponahatsa e le mpe le ho feta.

Tshebetso ea teko

Kahoo, re na le mekhoa e 'meli ea ho hlophisa data ho bukantswe - hstore/json_object. Ntle le moo, lihlopha tsa linotlolo le boleng ka botsona li ka hlahisoa ka mekhoa e 'meli, ka phetoho ea kahare kapa kantle ho mongolo: array_agg(i::text) / array_agg(i)::text[].

Ha re hlahlobeng katleho ea mefuta e fapaneng ea serialization re sebelisa mohlala oa maiketsetso - serialize linomoro tse fapaneng tsa linotlolo:

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

Mongolo oa tlhahlobo: serialization

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: ha re otle JOIN e boima ka bukantswe

Ho PostgreSQL 11, hoo e ka bang boholo ba dikishinari ba linotlolo tsa 2^12 serialization ho json e nka nako e nyane. Tabeng ena, e sebetsang ka ho fetisisa ke motsoako oa json_object le phetoho ea mofuta oa "internal". array_agg(i::text).

Joale a re leke ho bala bohlokoa ba senotlolo ka seng ka makhetlo a 8 - ka mor'a moo, haeba u sa fumane buka e hlalosang mantsoe, ke hobane'ng ha e hlokahala?

Sengoloa sa tlhahlobo: ho baloa ho tsoa bukeng e hlalosang mantsoe

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: ha re otle JOIN e boima ka bukantswe

'Me ... e se e le hoo e ka bang ka 2^6 linotlolo, ho bala ho tsoa bukeng ea json ho qala ho lahleheloa ke makhetlo a mangata ho bala ho tsoa hstore, hobane jsonb ho etsahala se tšoanang ho 2^9.

Qetello ea ho qetela:

  • haeba u hloka ho e etsa KENA ka lirekoto tse ngata tse pheta-phetoang - ho molemo ho sebelisa "dictionary" ea tafole
  • haeba bukantswe ya hao e lebeletswe e nyane mme o ka se bale haholo ho yona - o ka sebelisa json[b]
  • maemong ohle a mang hstore + array_agg(i:: mongolo) e tla sebetsa haholoanyane

Source: www.habr.com

Eketsa ka tlhaloso