ProHoster > Blogi > Haldamine > PostgreSQL Antipatterns: anname Àgeda JOIN'ile vastu sÔnaraamatuga

PostgreSQL Antipatterns: anname Àgeda JOIN'ile vastu sÔnaraamatuga

JĂ€tkame artiklite seeriat, mis on pĂŒhendatud vĂ€hemtuntud meetodite uurimisele, mis parendavad PostgreSQL-is "nĂ€iliselt lihtsate" pĂ€ringute jĂ”udlust:

Ärge arvake, et ma ei armasta JOIN-i nii vĂ€ga
 🙂

Kuid sageli on pĂ€ring ilma selleta tunduvalt efektiivsem. SeetĂ”ttu proovime tĂ€na vabaneda ressursimahlakast JOIN-ist — sĂ”naraamatute abil.

PostgreSQL Antipatterns: anname Àgeda JOIN'ile vastu sÔnaraamatuga

Alates PostgreSQL 12-st vÔivad allpool kirjeldatud olukorrad veidi erinevalt esineda CTE mitte-materialiseerimise tÔttu vaikimisi. Seda kÀitumist saab taastada eelmisele, mÀÀrates vÔtme MATERIALIZED.

Palju „fakte” piiratud sĂ”naraamatule

VĂ”tame ĂŒsna reaalse rakendusĂŒlesande — peame koostama loendi saadetud sĂ”numitest vĂ”i aktiivsetest ĂŒlesannetest saatjatelt:

25.01 | Ivanov I.I. | Uue algoritmi kirjelduse ettevalmistamine.
22.01 | Ivanov I.I. | Artikli kirjutamine Habrile: elu ilma JOIN-ita.
20.01 | Petrov P.P. | Aita pÀringut optimeerida.
18.01 | Ivanov I.I. | Artikli kirjutamine Habrile: JOIN andmete jaotuse arvestamisel.
16.01 | Petrov P.P. | Aita pÀringut optimeerida.

Abstractses maailmas oleks ĂŒlesannete autorid pidanud olema ĂŒhtlaselt jaotatud kĂ”igi meie organisatsiooni töötajate vahel, kuid tegelikkuses tulevad ĂŒlesanded tavaliselt ĂŒsna piiratud hulgalt inimeselt — â€žĂŒlemuselt” ĂŒlespoole hierarhias vĂ”i „kollastega” naaberosakondadest (analĂŒĂŒtikud, disainerid, turundus jne).

VĂ”tame arvesse, et meie organisatsioonis 1000 inimesest vaid 20 autorit (tavaliselt isegi vĂ€hem) mÀÀravad ĂŒlesanne iga konkreetse tĂ€itja aadressile ja kasutame seda ainealast teadmist, et kiirendada „traditsioonilist” pĂ€ringut.

Skripti generaator

-- töötajad
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);

-- ĂŒlesanded antud jaotusega
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);

Kuvame viimased 100 ĂŒlesannet konkreetse tĂ€itja jaoks:

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: anname Àgeda JOIN'ile vastu sÔnaraamatuga
[vaata explain.tensor.ru]

Selgub, et 1/3 kogu ajast ja 3/4 lugemist andmeleidmise lehtedest tehti ainult selleks, et 100 korda otsida autorit — igas vĂ€ljaantud ĂŒlesandes. Kuid me teame, et selle saja seast on alles 20 erinevat — kas vĂ”iksime seda teadmist kasutada?

hstore-sÔnaraamat

Kasutame seda tĂŒĂŒbiga hstore vĂ”tme-vÀÀrtuse „sĂ”naraamatu” genereerimiseks:

CREATE EXTENSION hstore

SÔnaraamatusse piisab, kui paigutada autori ID ja tema nimi, et saaksime hiljem selle vÔtme alusel hankida:

-- loome sihtvaliku
WITH T AS (
  SELECT
    *
  FROM
    task
  WHERE
    owner_id = 777
  ORDER BY
    task_date DESC
  LIMIT 100
)
-- loome sÔnaraamatu unikaalsete vÀÀrtuste jaoks
, 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
    ))
)
-- saame seotud sÔnaraamatu vÀÀrtused
SELECT
  *
, (TABLE dict) -> author_id::text -- hstore -> key
FROM
  T;

PostgreSQL Antipatterns: anname Àgeda JOIN'ile vastu sÔnaraamatuga
[vaata explain.tensor.ru]

Informatsiooni hankimiseks inimestest kulus kaheksa korda vĂ€hem aega ja seitse korda vĂ€hem andmeid lugeda! Lisaks „sĂ”naraamatustamisele” aitas neid tulemusi saavutada ka massiline kirje hankimine lauast ĂŒhe kĂ€igu ajal koos = ANY(ARRAY(...)).

Tabeli kirjed: serialiseerimine ja deserialiseerimine

Aga mis siis, kui me peame sĂ€ilitama sĂ”naraamatus mitte ĂŒhe teksti vĂ€lja, vaid terve kirje? Sel juhul aitab PostgreSQL-l töötada tabeli kirje kui ĂŒhe vÀÀrtusega:

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

Vaadakem, mis siin tegelikult toimus:

  1. VÔtsime p kui alias kogu tabeli kirjele person ja kogusime neist massiivi.
  2. Seda massive vahetasime massive tekstireadeks (person[]::text[]), et paigutada see hstore-sÔnaraamatusse vÀÀrtuste massiivina.
  3. Saades seotud kirje, tÔime selle sÔnaraamatust vÔtme alusel kui tekstirea.
  4. Tekstime peame muutama tabeli tĂŒĂŒbi vÀÀrtuseks person (iga tabeli jaoks luuakse automaatselt sama nimega tĂŒĂŒp).
  5. „Avastasime” tĂŒĂŒbitud kirje veergudena (...).*.

json-sÔnaraamat

Kuid selline trikk, nagu me ĂŒlal rakendasime, ei tööta, kui ei ole vastavat tabeli tĂŒĂŒpi, et teha „tĂŒĂŒpide ĂŒmberjaotamine”. TĂ€pselt sama olukord tekib, kui proovime andmeallikana kasutada CTE rida, mitte „reaalset” tabelit.

Sel juhul aitavad meid funktsioonid json-iga töötamiseks:

...
, p AS ( -- see on juba CTE
  SELECT
    *
  FROM
    person
  WHERE
    ...
)
, dict AS (
  SELECT
    json_object( -- nĂŒĂŒd on see juba json
      array_agg(id)::text[]
    , array_agg(row_to_json(p))::text[] -- ja sees json iga rea jaoks
    )
  FROM
    p
)
SELECT
  *
FROM
  T
, LATERAL(
    SELECT
      *
    FROM
      json_to_record(
        ((TABLE dict) ->> author_id::text)::json -- tÔmmatud sÔnast nagu json
      ) AS j(name text, birth_date date) -- tÀitsime vajaliku struktuuri
  ) j;

Oluline on mĂ€rkida, et sihttava kirjelduse juures vĂ”ime loetleda mitte kĂ”ik algse rea vĂ€ljad, vaid ainult need, mis on meile tĂ”eliselt vajalikud. Kui meil on aga „kohalik” tabel, on parem kasutada funktsiooni json_populate_record.

SĂ”nastikku pÀÀseme endiselt ĂŒhe korra, kuid json-desserialiseerimise kulud on piisavalt suured, seetĂ”ttu on sellisel viisil mĂ”istlik kasutada ainult mĂ”nel juhul, kui „aus” CTE skaneerimine osutub halvemaks.

Testime jÔudlust

Nii oleme saanud kaks viisi andmete serialiseerimiseks sĂ”nastikku — hstore / json_object. Peale selle saab vĂ”tmete ja vÀÀrtuste massiive luua samuti kahel viisil, kas sisemise vĂ”i vĂ€limise tekstiks muutmisega: array_agg(i::text) / array_agg(i)::text[].

Kontrollime erinevate serialiseerimisviiside efektiivsust tĂ€iesti sĂŒnteetilisel nĂ€itel — serialiseerime erinevat arvu vĂ”tmeid:

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

Hindamisvorming : serialiseerimine

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: anname Àgeda JOIN'ile vastu sÔnaraamatuga

PostgreSQL 11-s umbes 2^12 vĂ”tme suuruse korral json-isse serialiseerimine vajab vĂ€hem aega . Samuti on kĂ”ige tĂ”husam kombinatsioon json_object ja „sise” tĂŒĂŒpide muutminearray_agg(i::text) NĂŒĂŒd proovime lugeda iga vĂ”tme vÀÀrtust 8 korda — sest kui sĂ”nastikku ei pöörduda, siis milleks see vajalik on?.

Hindamisvorming : lugemine sÔnastikust

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 < (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;

WITH T AS (
  VALI
    *
  , (
      VALI
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          VALI
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                VALI
                  json_object(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              VALI
                (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
)
VALI
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

PostgreSQL Antipatterns: anname Àgeda JOIN'ile vastu sÔnaraamatuga

Ja
 umbes kui 2^6 vÔtme lugemine json-sÔnastikust hakkab oluliselt kehvem olema kui hstore-lugemine, jsonb puhul juhtub see sama 2^9 juures.

LÔpptulemused:

  • kui on vaja teha JOIN korduvalt esinevate salvestustega — on parem kasutada tabeli „sĂ”nastamist”
  • kui teie sĂ”nastik on jĂ€rjepidevalt vĂ€ike ja loete aeg-ajalt selle sisu — vĂ”ib kasutada json[b]
  • kĂ”igil teistel juhtudel hstore + array_agg(i::text) on tĂ”husam

Allikas: habr.com

Osta usaldusvÀÀrne veebihosting DDoS kaitsega, VPS VDS serverid đŸ”„ Osta usaldusvÀÀrne veebihosting DDoS kaitsega, VPS VDS serverid | ProHoster