Cuntooyinka loogu talagalay Weydiimaha SQL Buka

Bilood ka hor waanu ku dhawaaqnay sharax.tensor.ru - dadweynaha adeegga falanqaynta iyo sawiridda qorshayaasha weydiinta ku saabsan PostgreSQL.

Waxaad hore u isticmaashay in ka badan 6000 jeer, laakiin hal sifo waxtar leh oo laga yaabo in aan la dareemin ayaa ah tilmaamo dhismo, oo u eg sidan:

Dhagayso iyaga, oo codsiyadaadu waxay noqon doonaan "mid siman oo jilicsan." 🙂

Laakin si dhab ah, xaalado badan oo ka dhigaya codsi gaabis ah oo kheyraad gaajeysan waa kuwo caadi ah waxaana lagu aqoonsan karaa qaabka iyo xogta qorshaha.

Xaaladdan oo kale, qof kasta oo horumariye ah maaha inuu raadiyo ikhtiyaarka hagaajinta naftiisa, isaga oo ku tiirsan oo keliya khibradiisa - waxaan u sheegi karnaa waxa halkan ka dhacaya, maxay noqon kartaa sababta, iyo sida loo wajaho xal. Taasi waa waxa aanu samaynay.

Aynu si qoto dheer u eegno kiisaskan - sida loo qeexay iyo waxa ay yihiin talooyin ay u horseedaan.

Si aad si fiican ugu dhex milmiso mawduuca, waxaad marka hore dhegaysan kartaa qaybta u dhiganta warbixintayda PGConf.Russia 2020, ka dibna kaliya u gudub falanqaynta faahfaahsan ee tusaale kasta:

#1: index "undersorting"

Markay soo baxdo

Tus qaansheegtii ugu dambeysay ee macmiilka "LLC Kolokolchik".

Sida loo aqoonsado

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

talooyinka

Index la isticmaalo ku balaarin beeraha kala soocida.

Tusaale:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- отбор по конкретной связи
ORDER BY
  pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;

[fiiri sharaxaad.tensor.ru]

Isla markiiba waxaad ogaan kartaa in in ka badan 100 diiwaan laga soo jaray tusaha, kuwaas oo markaa la kala saaray, ka dibna mid keliya ayaa hadhay.

Sixitaanka:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

[fiiri sharaxaad.tensor.ru]

Xataa tusaalaha hore ee noocan oo kale ah - 8.5 dhakhso badan iyo 33 jeer ka yar wax la akhriyo. Inta badan "xaqiiqda" aad u leedahay qiimo kasta, ayaa sii muuqda saamaynta fk.

Waxaan ogsoonahay in tusaha noocan ahi uu u shaqayn doono sidii tusaha "horgagga" oo aan ka xumaan sidii hore ee su'aalaha kale fk, meesha kala sooc pk ma jirin mana jirin (waxaad ka akhrisan kartaa wax badan oo arrintan ku saabsan Maqaalkayga ku saabsan helitaanka tusmooyinka aan waxtarka lahayn). Marka lagu daro, waxay ku siin doontaa caadi taageero shisheeye oo cad garoonkan.

#2: isgoys index (BitmapAnd)

Markay soo baxdo

Tus dhammaan heshiisyada macmiilka "LLC Kolokolchik", oo lagu soo gabagabeeyay magaca "NAO Buttercup".

Sida loo aqoonsado

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

talooyinka

abuuro index ka kooban by beero ka soo jeeda labada asalka ah ama ballaariyo mid ka mid ah kuwa jira oo beero ka labaad.

Tusaale:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре

[fiiri sharaxaad.tensor.ru]

Sixitaanka:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

[fiiri sharaxaad.tensor.ru]

Qiimaha halkan waa ka yar yahay, maadaama Bitmap Heap Scan uu iskii wax u tari karo. Laakiin si kastaba 7 dhakhso badan iyo 2.5 jeer ka yar wax la akhriyo.

#3: Isku dar tusmooyinka (BitmapOr)

Markay soo baxdo

Tus 20ka ugu da'da weyn "annaga" ama codsiyada aan la magacaabin ee habaynta, adiga oo mudnaanta leh.

Sida loo aqoonsado

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

talooyinka

Si aad u isticmaasho UNION [ALL] in la isku daro su'aalo-hoosaadyada mid kasta oo ka mid ah OR-blocks ee shuruudaha.

Tusaale:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- свои
  fk_own IS NULL -- ... или "ничьи"
ORDER BY
  pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;

[fiiri sharaxaad.tensor.ru]

Sixitaanka:

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 -- сначала "свои" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL -- потом "ничьи" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо

[fiiri sharaxaad.tensor.ru]

Waxaan ka faa'iidaysanay xaqiiqda ah in dhammaan 20-kii diiwaan ee loo baahnaa isla markiiba la helay qaybta koowaad, sidaas darteed kan labaad, oo leh "qaali ah" Bitmap Heap Scan, xitaa lama fulin - dhamaadka 22x dhakhso badan, 44x akhrin yar!

Sheeko faahfaahsan oo ku saabsan habkan wanaajinta iyadoo la adeegsanayo tusaalooyin gaar ah waxaa lagu akhriyi karaa maqaallo PostgreSQL Antipatterns: KU biirista waxyeelada leh iyo OR-yada и PostgreSQL Antipatterns: sheeko dib-u-habayn ku saabsan raadinta magac ahaan, ama "Horumarinta gadaal iyo hor".

Nooca guud xulashada la dalbaday oo ku salaysan dhowr fure (oo kaliya ma aha const / NULL lamaanaha) ayaa looga hadlay maqaalka SQL HowTo: In si toos ah wax loogu qoro su'aasha, ama "Saddex-tilaabo ee Dugsiga Hoose".

#4: Waxaan akhrinay waxyaabo badan oo aan loo baahnayn

Markay soo baxdo

Sida caadiga ah, waxay soo baxdaa markaad rabto inaad "ku xirto filtar kale" codsi horeba u jiray.

"Oo ma lihid mid la mid ah, laakiin oo leh badhamada hooyada-luul? » filimka "The Diamond Arm"

Tusaale ahaan, wax ka beddelka hawsha kor ku xusan, tus 20ka ugu horreeya codsiyada "muhiimka ah" ee habaynta, iyadoon loo eegin ujeeddadooda.

Sida loo aqoonsado

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

talooyinka

Abuur [dheeraad] takhasus leh index leh xaalad HALKEE ama ku dar meelo dheeraad ah tusmada.

Haddii xaaladda shaandheynta ay tahay "mid taagan" ujeedooyinkaaga - taasi waa ma tilmaamayso balaadhinta liiska qiyamka mustaqbalka - waxa fiican in la isticmaalo index WHERE. Heerarka boolean/enum ee kala duwan ayaa si fiican ugu habboon qaybtan.

Haddii xaaladda shaandhaynta waxay qaadan kartaa macnayaal kala duwan, markaa way fiicantahay in la balaadhiyo tusaha meelahan - sida xaaladda BitmapAnd kor ku xusan.

Tusaale:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

[fiiri sharaxaad.tensor.ru]

Sixitaanka:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[fiiri sharaxaad.tensor.ru]

Sida aad arki karto, shaandhaynta ayaa gebi ahaanba ka luntay qorshaha, codsiguna wuxuu noqday 5 jeer ka dheereeya.

#5: miis yar

Markay soo baxdo

Isku dayo kala duwan oo lagu abuurayo safka habaynta hawshaada, marka tiro badan oo cusbooneysiin ah / tirtirida diiwaanada miiska u horseedo xaalad tiro badan oo diiwaano "dhintay".

Sida loo aqoonsado

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

talooyinka

Si joogto ah u samee gacanta VACUUM [FULL] ama aad ku guulaysato tababar joogto ah oo ku filan autovacuum iyadoo si fiican loo hagaajinayo cabbirkeeda, oo ay ku jiraan miis gaar ah.

Inta badan, dhibaatooyinka noocan oo kale ah waxaa sababa halabuurka weydiinta liidata marka laga wacayo macquulka ganacsiga sida kuwa looga hadlay PostgreSQL Antipatterns: la dagaalanka tirada badan ee " dhintay".

Laakiin waxaad u baahan tahay inaad fahanto in xitaa VACUUM FULL laga yaabo inaysan had iyo jeer ku caawin. Xaaladahan oo kale, waxaa habboon in aad barato algorithm-ka maqaalka DBA: marka VACUUM uu fashilmo, waxaanu ku nadiifin miiska gacanta.

#6: Wax ka akhrinta "dhexe" ee tusmada

Markay soo baxdo

Waxay u muuqataa inaan wax yar akhrinay, wax walbana waa la tusmeeyay, qofna kamaanu shaandhayn xad-dhaaf - laakiin wali waxaan akhrinay bogag ka badan inta aan jeclaan lahayn.

Sida loo aqoonsado

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

talooyinka

U fiirso qaab dhismeedka tusaha la isticmaalay iyo meelaha muhiimka ah ee lagu sheegay weydiinta - waxay u badan tahay qayb ka mid ah tusmada lama dejin. Waxay u badan tahay inaad abuurto tusmo la mid ah, laakiin la'aanteed horgalayaasha ama baro in ay qiimeeyaan qiimahooda.

Tusaale:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;

[fiiri sharaxaad.tensor.ru]

Wax kastaa waxay u muuqdaan inay fiican yihiin, xitaa marka loo eego tusmada, laakiin si uun ayaa shaki leh - mid kasta oo ka mid ah 20-ka diiwaan ee la akhriyay, waxay ahayd inaan ka jarno 4 bog oo xog ah, 32KB rikoodhkiiba - miyaanay taasi geesinimo ahayn? Iyo magaca tusmada tbl_fk_org_fk_cli_idx fikir kicin.

Sixitaanka:

CREATE INDEX ON tbl(fk_cli);

[fiiri sharaxaad.tensor.ru]

Si lama filaan ah - 10 jeer ka dheereeya, iyo 4 jeer ka yar in la akhriyo!

Tusaalooyinka kale ee xaaladaha isticmaalka aan waxtarka lahayn ee tusmooyinka ayaa lagu arki karaa maqaalka DBA: helidda tusmooyin aan faa'iido lahayn.

#7: CTE × CTE

Markay soo baxdo

Codsi wuxuu dhaliyay "dufan" CTE laga soo bilaabo miisas kala duwan, ka dibna go'aansaday in ay sameeyaan dhexdooda JOIN.

Kiisku wuxuu khuseeyaa noocyada ka hooseeya v12 ama codsiyada leh WITH MATERIALIZED.

Sida loo aqoonsado

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

talooyinka

Si taxadar leh u baar codsiga - iyo CTE-yada ma looga baahan yahay halkan gabi ahaanba?? Hadday haa tahay, markaa dalbo "qaamuuska" hstore/json marka loo eego qaabka lagu tilmaamay PostgreSQL Antipatterns: aynu ku garaacno ku biirka culus qaamuuska.

#8: ku beddel diskka (kuwa qoran)

Markay soo baxdo

Habayn hal mar ah (kala-saarid ama kala saarid) tiro badan oo diiwaanno ah kuma habboona xusuusta loo qoondeeyay tan.

Sida loo aqoonsado

-> *
   && temp written > 0

talooyinka

Haddii qadarka xusuusta ee uu isticmaalo qalliinku aanu aad uga badanayn qiimaha la cayimay ee cabbirka shaqada_mem, waxaa mudan in la saxo. Waxaad isla markiiba u geli kartaa qaabeynta qof walba, ama waad dhex mari kartaa SET [LOCAL] codsi / macaamil gaar ah.

Tusaale:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

[fiiri sharaxaad.tensor.ru]

Sixitaanka:

SET work_mem = '128MB'; -- перед выполнением запроса

[fiiri sharaxaad.tensor.ru]

Sababo cad dartood, haddii xusuusta kaliya la isticmaalo oo aan diskka la isticmaalin, markaa su'aasha si dhakhso leh ayaa loo fulin doonaa. Isla mar ahaantaana, qayb ka mid ah rarka HDD sidoo kale waa la saarayaa.

Laakiin waxaad u baahan tahay inaad fahamto inaadan had iyo jeer awoodi doonin inaad qoondayso wax badan iyo xusuus badan - ma jiraan wax ku filan qof walba.

#9: tirakoob aan khusayn

Markay soo baxdo

Wax badan bay hal mar ku shubeen kaydka xogta, laakiin ma helin wakhti ay ku kaxeeyaan ANALYZE.

Sida loo aqoonsado

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

talooyinka

Samee ANALYZE.

Xaaladdan ayaa si faahfaahsan loogu sharraxay PostgreSQL Antipatterns: tirakoobyadu waa wax walba.

#10: "wax baa khaldamay"

Markay soo baxdo

Waxaa jiray sugitaan quful ah oo lagu soo rogay codsi tartamaya, ama waxaa jiray qalab ku filan oo CPU/sareeye ah.

Sida loo aqoonsado

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

talooyinka

Isticmaal dibadda nidaamka la socodka server-ka xannibaya ama isticmaalka kheyraadka aan caadiga ahayn. Waxaan mar hore ka hadalnay noocayaga abaabulka nidaamkan boqolaal server ah halkan и halkan.

Source: www.habr.com