ProHoster > Blog > Administrimi > PostgreSQL Antipatterns: goditim me fjalorin në JOIN e rëndë

PostgreSQL Antipatterns: goditim me fjalorin në JOIN e rëndë

Vazhdoni me serinë e artikujve që trajtojnë eksplorimin e mënyrave të panjohura për të përmirësuar performancën e kërkesave "në dukje të thjeshta" në PostgreSQL:

Mos mendoni se unĂ« e urrej kaq shumĂ« JOIN... 🙂

Por shpesh herĂ«, pa tĂ«, kĂ«rkesa rezulton tĂ« jetĂ« dukshĂ«m mĂ« e shpejtĂ« se me tĂ«. Prandaj sot do tĂ« provojmĂ« tĂ« largohemi nga JOIN-i qĂ« konsumon burime — me ndihmĂ«n e njĂ« fjalori.

PostgreSQL Antipatterns: goditim me fjalorin në JOIN e rëndë

Duke filluar nga PostgreSQL 12, disa nga situatat e përshkruara më poshtë mund të reproducohen pak ndryshe për shkak të mos-materializimit të CTE po default. Ky qëndrim mund të kthehet në të mëparshmin me ndihmën e një çelësi MATERIALIZED.

Shumë "fakte" nga një fjalor i kufizuar

Le tĂ« marrim njĂ« detyrĂ« tĂ« realizuar — duhet tĂ« nxjerrim njĂ« listĂ« mesazheve tĂ« hyrjes apo detyrave aktive me dĂ«rguesit:

25.01 | Ivanov I.I. | Të përgatitësh përshkrimin e algoritmit të ri.
22.01 | Ivanov I.I. | Të shkruash një artikull në Habr: jeta pa JOIN.
20.01 | Petrov P.P. | Të ndihmosh në optimizimin e kërkesës.
18.01 | Ivanov I.I. | Të shkruash një artikull në Habr: JOIN me llogaritjen e shpërndarjes së të dhënave.
16.01 | Petrov P.P. | Të ndihmosh në optimizimin e kërkesës.

NĂ« njĂ« botĂ« abstrakte, autorĂ«t e detyrave duhet tĂ« ishin tĂ« shpĂ«rndarĂ« barabartĂ« midis tĂ« gjithĂ« punonjĂ«sve tĂ« organizatĂ«s sonĂ«, por nĂ« realitet detyrat vijnĂ«, zakonisht, nga njĂ« numĂ«r mjaft i kufizuar njerĂ«zish — "nga menaxhmenti" lart nĂ« hierarki ose "nga kolegĂ«t" nga departamentet pĂ«rkatĂ«se (analistĂ«, dizajnerĂ«, marketing, 
).

Le të pranojmë se në organizatën tonë nga 1000 njerëz, vetëm 20 autorë (zakonisht edhe më pak) i japin detyra çdo ekzekutuesi të veçantë dhe të përdorim këtë njohuri tematike, për të nxitur "kërkesën tradicionale".

Skema-gjeneruese

-- punonjësit
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);

-- detyrat me shpërndarje të dhënë
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);

Le të tregojmë 100 detyrat e fundit për një ekzekutues të veçantë:

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: goditim me fjalorin në JOIN e rëndë
[shiko në explain.tensor.ru]

KĂ«shtu, 1/3 e gjithĂ« kohĂ«s dhe 3/4 e leximeve e faqeve tĂ« dhĂ«nash u bĂ«nĂ« vetĂ«m pĂ«r tĂ« kĂ«rkuar autorrin 100 herĂ« — pĂ«r çdo detyrĂ« tĂ« shfaqur. Por ne e dimĂ« se mes kĂ«tyre qindrave pĂ«r njĂ« total prej 20 tĂ« ndryshĂ«m — a nuk mund ta pĂ«rdorim kĂ«tĂ« njohuri?

Fjalori hstore

Le të përdorim tipi hstore për të gjeneruar një "fjalor" çelës-vlerë:

CREATE EXTENSION hstore

Në fjalor mjafton të vendosim ID-në e autorit dhe emrin e tij, për të pasur mundësinë të nxjerrim sipas këtij çelësi:

-- formojmë zgjedhjen e synuar
WITH T AS (
  SELECT
    *
  FROM
    task
  WHERE
    owner_id = 777
  ORDER BY
    task_date DESC
  LIMIT 100
)
-- formojmë fjalorin për vlera unike
, 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
    ))
)
-- marrim vlerat e lidhura nga fjalori
SELECT
  *
, (TABLE dict) -> author_id::text -- hstore -> key
FROM
  T;

PostgreSQL Antipatterns: goditim me fjalorin në JOIN e rëndë
[shiko në explain.tensor.ru]

Për marrjen e informacionit rreth personave, është shpenzuar në 2 herë më pak kohë dhe 7 herë më pak të dhëna! Përveç "fjalorizimit", qëllim të këtyre rezultateve arritëm gjithashtu nxjerrjen masive të regjistrimeve nga tabela në një kalim me ndihmën e = ANY(ARRAY(...)).

Regjistrimet e tabelës: serializimi dhe deserializimi

Por çfarë të bëjmë nëse duam të ruajmë në fjalor jo vetëm një fushë tekstuale, por një të tërë regjistrim? Në këtë rast, do të na ndihmojë aftësia e PostgreSQL për të punuar me regjistrimin e tabelës si një vlerë të vetme:

...
, dict AS (
  SELECT
    hstore(
      array_agg(id)::text[]
    , array_agg(p)::text[] -- magji #1
    )
  FROM
    person p
  WHERE
    ...
)
SELECT
  *
, (((TABLE dict) -> author_id::text)::person).* -- magji #2
FROM
  T;

Le të shpjegojmë se çfarë ndodhi këtu:

  1. Ne e morëm p si alias për regjistrimin e plotë të tabelës person dhe krijuam një masë prej tyre.
  2. Kjo masë regjistrimesh u ri-konvertua në një masë vargjesh tekstuale (person[]::text[]), për ta vendosur në fjalorin hstore si një masë vlerash.
  3. Kur merrnim regjistrimin e lidhur, e nxorrëm atë nga fjalori sipas çelësit si një varg tekstual. Teksin e kemi nevojë
  4. ta kthejmë në një vlerë të tipit tabelar person (për çdo tabelë krijohet automatikisht një tip i saj me emrin e njëjtë). "Zgjodhëm" regjistrimin e tipizuar në kolona me ndihmën e
  5. fjalorit json (...).*.

json-fjalor

Porosja e këtij triku, siç aplikuam më sipër, nuk do të funksionojë nëse nuk ka një lloj tabelar për të bërë "cast". Të njëjtin problem do të hasim edhe nëse për burim të të dhënave për serializimin përpiqemi të përdorim një varg CTE, e jo një "tabelë reale"..

Në këtë rast, do të na ndihmojnë funksionet për të punuar me json.:

...
, p AS ( -- kjo tashmë është CTE
  SELECT
    *
  FROM
    person
  WHERE
    ...
)
, dict AS (
  SELECT
    json_object( -- tani është json
      array_agg(id)::text[]
    , array_agg(row_to_json(p))::text[] -- dhe brenda json për çdo rresht
    )
  FROM
    p
)
SELECT
  *
FROM
  T
, LATERAL(
    SELECT
      *
    FROM
      json_to_record(
        ((TABLE dict) ->> author_id::text)::json -- e nxorrëm nga fjalori si json
      ) AS j(name text, birth_date date) -- mbushëm strukturën që na nevojitet
  ) j;

Duhet theksuar se kur përshkruajmë strukturën target, ne mund të përmendim jo të gjitha fushat e rreshtit fillestar, por vetëm ato që na nevojiten vërtet. Nëse kemi një tabelë "natyrore", është më mirë të përdorim funksionin json_populate_record..

Qasja në fjalor ndodh për një herë, por kostot e json-[de]serializimit janë mjaft të larta,prandaj ky mënyrë është e arsyeshme të përdoret vetëm në disa raste, kur "skanimi i n Honest" CTE tregon se është më keq.

Testojmë performancën.

Pra, ne kemi dy mĂ«nyra pĂ«r serializimin e tĂ« dhĂ«nave nĂ« fjalor — hstore / json_object.PĂ«rveç kĂ«saj, vetĂ« arrays e çelĂ«save dhe vlerave mund tĂ« krijohen gjithashtu nĂ« dy mĂ«nyra, me konvertim tĂ« brendshĂ«m ose tĂ« jashtĂ«m nĂ« tekst: array_agg(i::text) / array_agg(i)::text[].

TĂ« kontrollojmĂ« efikasitetin e llojeve tĂ« ndryshme tĂ« serializimit nĂ« njĂ« shembull tĂ« pastĂ«r sintetik — serializojmĂ« numra tĂ« ndryshĂ«m çelĂ«sash.:

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

Skema e vlerësimit: serializimi.

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: goditim me fjalorin në JOIN e rëndë

Në PostgreSQL 11, deri në madhësinë e fjalorit me 2^12 çelësa, serializimi në json kërkon më pak kohë.Në këtë rast, kombinimi më efektiv është json_object dhe konvertimi "i brendshëm" i llojeve array_agg(i::text)..

Tani le tĂ« provojmĂ« tĂ« lexojmĂ« vlerĂ«n e çdo çelĂ«si 8 herĂ« — sepse nĂ«se nuk e qasemi fjalorit, pĂ«rse na nevojitet ai?

Skema e vlerësimit: lexim nga fjalori.

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: goditim me fjalorin në JOIN e rëndë

Dhe... tashmë rreth me 2^6 çelësa, leximi nga fjalori json fillon të humbasë shumë në krahasim me leximin nga hstore, për jsonb e njëjta gjë ndodh me 2^9.

Konkluzionet përfundimtare:

  • nĂ«se duhet tĂ« bĂ«ni JOIN me shĂ«nime tĂ« shumĂ«fishta, — Ă«shtĂ« mĂ« mirĂ« tĂ« pĂ«rdorni "shndĂ«rrimin" e tabelĂ«s.
  • nĂ«se fjalori juaj pritet tĂ« jetĂ« i vogĂ«l dhe do tĂ« lexoni prej tij pak, — mund tĂ« pĂ«rdorni json[b].
  • nĂ« tĂ« gjitha rastet e tjera, hstore + array_agg(i::text) do tĂ« jetĂ« mĂ« efikase.

Burimi: habr.com

Bleni hostim tĂ« besueshĂ«m pĂ«r faqe me mbrojtje nga DDoS, serverĂ« VPS VDS đŸ”„ Bleni hostim tĂ« besueshĂ«m pĂ«r faqe me mbrojtje nga DDoS, serverĂ« VPS VDS | ProHoster