🥇PostgreSQL Antipatterns: aynu ku garaacno culus ku biir qaamuuska

Waxaan sii wadeynaa taxanaha maqaallada u heellan daraasadda siyaabaha yar-yar ee loo yaqaanno si loo horumariyo waxqabadka "wax fudud" PostgreSQL weydiimaha:

Ha u malayn in aanan jeclayn ku soo biir... :)

Laakiin inta badan la'aanteed, codsigu wuxuu noqdaa mid si weyn uga faa'iido badan marka loo eego isaga. Markaa maanta waxaan isku dayi doonaa ka takhalus kheyraadka degdega ah ku biir - isticmaalka qaamuuska.

Laga bilaabo PostgreSQL 12, xaaladaha qaarkood ee hoos lagu sharraxay waxaa laga yaabaa in loo soo saaro si ka duwan. CTE-da aan caadiga ahayn. Dhaqankan waxaa lagu celin karaa iyadoo la cayimayo furaha MATERIALIZED.

"Xaqiiqo" badan oo erayo xaddidan ku jira

Aynu qaadano hawl codsi oo dhab ah - waxaan u baahanahay inaan muujino liis fariimaha soo socda ama hawlo firfircoon oo lala yeesho soo diraha:

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

Dunida aan la taaban karin, qorayaasha hawsha waa in si siman loogu qaybiyaa dhammaan shaqaalaha ururkayaga, laakiin dhab ahaantii hawluhu waxay ka yimaadaan, sida caadiga ah, tiro dad ah oo xadidan - "laga bilaabo maamulka" ilaa madaxda sare ama "qandaraaslayaasha hoose" ee waaxaha deriska ah (falanqeeyayaasha, naqshadeeyayaasha, suuqgeynta, ...).

Aynu aqbalno in ururkayaga 1000 qof, kaliya 20 qoraa (badanaa xitaa ka yar) ayaa dejinaya hawlo kasta oo gaar ah iyo Aynu isticmaalno aqoonta mawduucansi loo dedejiyo weydiinta "caadiga ah".

Soosaaraha qoraalka

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

Aynu tusno 100 ka hawl ee ugu dambeeya ee fuliye gaar ah:

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;

[fiiri sharaxaad.tensor.ru]

Waxay soo baxaysaa in 1/3 wadarta wakhtiga iyo 3/4 akhrin boggaga xogta waxaa loo sameeyay kaliya in la raadiyo qoraaga 100 jeer - hawl kasta oo soo saarid ah. Laakiin waxaan ognahay in boqolaal kuwan ka mid ah kaliya 20 kala duwan - Suurtagal ma tahay in la isticmaalo aqoontan?

hstore-qaamuuska

Aynu ka faa'iidaysano nooca hstore si loo soo saaro "qaamuuska" qiimaha muhiimka ah:

CREATE EXTENSION hstore

Kaliya waxaan u baahanahay in aan galno aqoonsiga qoraaga iyo magaciisa qaamuuska si aan markaas uga soo saarno furahaan:

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

[fiiri sharaxaad.tensor.ru]

Kharash ku baxay helida macluumaadka ku saabsan dadka 2 jeer ka yar wakhti iyo 7 jeer ka yar xogta! Marka lagu daro "ereyada", waxa sidoo kale naga caawiyay inaan gaarno natiijooyinkaan dib u soo celinta rikoorka badan miiska ka soo baxa hal baas oo isticmaalaya = ANY(ARRAY(...)).

Gelida Miiska: Taxane iyo Kala-saarid

Laakiin maxaa dhacaya haddii aan u baahanahay inaan badbaadino hal goob qoraal, laakiin dhammaan gelitaanka qaamuuska? Xaaladdan oo kale, awoodda PostgreSQL ayaa naga caawin doonta ula dhaqan gelida miiska sidii hal qiimo:

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

Bal aan eegno waxa halkan ka socday:

Waanu qaadanay p sida magac u ah gelitaanka miiska qofka buuxa oo soo ururiyey tiro badan.
this Iskudubbada duubista ayaa dib loo dhigay U diyaarsan xargaha qoraalka (qof[]:: qoraal[]) si loo geliyo qaamuuska hstore si kala duwan oo qiyam ah.
Marka aan helno rikoor la xiriira, waxaan qaamuuska oo fure laga soo saaray sida xadhig qoraal ah.
Waxaan u baahanahay qoraal u rog qiimaha miiska nooca qof (miis kasta magac isku mid ah ayaa si toos ah loo abuurayaa).
"Balaadhi" diiwaanka la qoray ee tiirarka isticmaalaya (...).*.

json qaamuus

Laakiin khiyaamadan oo kale sidaan kor ku codsanay ma shaqeyn doonto haddii aysan jirin nooc miiska u dhigma si loo sameeyo "kabka". Dhab ahaantii xaalad isku mid ah ayaa soo bixi doonta, iyo haddii aan isku dayno inaan isticmaalno saf CTE ah, ma aha miis "dhab ah"..

Xaaladdan oo kale way na caawin doonaan hawlaha la shaqaynta 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;

Waa in la ogaadaa in marka la tilmaamayo qaab-dhismeedka bartilmaameedka, ma liis gareyn karo dhammaan beeraha string isha, laakiin kaliya kuwa aan runtii u baahanahay. Haddii aan haysano miiska "hooyo", markaa way fiicantahay in la isticmaalo shaqada json_populate_record.

Waxaan weli geli qaamuuska hal mar, laakiin json-[de] kharashyada taxanaha ah ayaa aad u sarreeya, sidaas darteed, waa macquul in habkan loo isticmaalo oo kaliya xaaladaha qaarkood marka "daacad ah" CTE Scan uu muujiyo naftiisa ka sii xun.

Waxqabadka tijaabinta

Markaa, waxaan helnay laba siyaabood oo aan xogta ugu kala taxanno qaamuuska - hstore/json_object. Intaa waxaa dheer, qaababka furayaasha iyo qiyamka laftooda ayaa sidoo kale loo abuuri karaa laba siyaabood, iyada oo gudaha ama dibadda loo beddelo qoraalka: array_agg(i:: qoraal) / array_agg(i): qoraal[].

Aynu eegno waxtarka noocyada kala duwan ee taxanayaasha annagoo adeegsanayna tusaale macmal ah taxan tirooyinka kala duwan ee furayaasha:

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

Qoraalka qiimaynta: taxane

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;

On PostgreSQL 11, ilaa qiyaastii cabbir qaamuus ah oo ah 2^12 furayaal taxanaha json waxay qaadataa wakhti yar. Xaaladdan oo kale, tan ugu waxtarka badan waa isku dhafka json_object iyo beddelka nooca "gudaha". array_agg(i::text).

Hadda aan isku dayno inaan akhrino qiimaha fure kasta 8 jeer - ka dib, haddii aadan helin qaamuuska, markaa maxaa loogu baahan yahay?

Qoraalka qiimaynta: akhrinta qaamuuska

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;

Iyo...ba ku dhawaad oo leh 2^6 furayaal, akhrinta qaamuuska json waxay bilaabataa inay lumiso marar badan akhrinta hstore, jsonb isla sidaas oo kale ayaa ku dhacda 2^9.

Gabagabada kama dambaysta ah:

haddii aad u baahan tahay inaad sameyso KU BIIR oo wata diiwaanno badan oo soo noqnoqda - waxaa fiican inaad isticmaasho "qaamuuska" miiska

haddii qaamuuskaga la filayo yar oo aad wax badan ka akhrin maysid - waxaad isticmaali kartaa json[b]

kiisaska kale oo dhan hstore + array_agg (i:: qoraal) waxay noqon doontaa mid waxtar badan

Source: www.habr.com

PostgreSQL Antipatterns: aynu ku garaacno ku biirka culus qaamuuska

"Xaqiiqo" badan oo erayo xaddidan ku jira

hstore-qaamuuska

Gelida Miiska: Taxane iyo Kala-saarid

json qaamuus

Waxqabadka tijaabinta

Add a comment cancel reply