Iiresiphi zemibuzo yeSQL egulayo

Kwiinyanga ezininzi ezidlulileyo sabhengeza explain.tensor.ru -uluntu inkonzo yokwahlulahlula kunye nokubona izicwangciso zemibuzo kwi-PostgreSQL.

Uyisebenzisile ngaphezulu kwamaxesha angama-6000 ukusukela ngoko, kodwa enye yezinto eziluncedo inokuba ayikhange iqatshelwe. imikhondo yolwakhiwo, ekhangeleka ngolu hlobo:

Mamela kubo kwaye izicelo zakho "ziya kuba silky silky". 🙂

Kodwa ngokunzulu, iimeko ezininzi ezenza isicelo sicothe kwaye "sidla-kudla" ngokwezibonelelo, ziqhelekile kwaye zinokuqatshelwa ngolwakhiwo kunye neenkcukacha zesicwangciso.

Kule meko, umphuhlisi ngamnye akayi kukhangela ukhetho lokuphucula yedwa, exhomekeke kuphela kumava akhe - sinokumxelela ukuba kwenzeka ntoni apha, ingaba yintoni isizathu, kwaye indlela yokuza nesisombululo. Nto leyo esayenzayo.

Makhe sihlolisise ezi meko - ukuba zichazwa njani kwaye zeziphi iingcebiso ezikhokelela kuzo.

Ukucwiliswa okungcono kwisihloko, unokuqala ukuphulaphula ibhloko ehambelana nayo ingxelo yam kwiPGConf.Russia 2020, kwaye kuphela emva koko uye kuhlalutyo oluneenkcukacha lomzekelo ngamnye:

#1: isalathisi "undersorting"

Nini

Bonisa i-invoyisi yokugqibela yomxhasi "LLC Kolokolchik".

Indlela yokuchonga

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

Iingcebiso

Isalathisi esisetyenzisiweyo yandisa ngamasimi ohlobo.

Umzekelo:

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;

[Jonga apha explain.tensor.ru]

Unokuqaphela ngokukhawuleza ukuba iirekhodi ezingaphezu kwe-100 zathatyathwa yi-index, eziye zahlelwa zonke, kwaye kwasala enye kuphela.

Siyalungisa:

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

[Jonga apha explain.tensor.ru]

Nakwisampulu yamandulo - 8.5x ngokukhawuleza kwaye 33x ukufundwa okumbalwa. Isiphumo siya kucaca ngakumbi, ngakumbi "iinyani" onazo ngexabiso ngalinye. fk.

Ndiqaphela ukuba isalathiso esinjalo siyakusebenza njenge "prefix" index akukho mbi kuneyangaphambili yeminye imibuzo fk, apho ihlelwa khona pk yayingekho kwaye ayikho (unokufunda ngakumbi malunga noku kwinqaku lam malunga nokufumana izalathisi ezingasebenziyo). Ngokukodwa, iya kubonelela ngesiqhelo inkxaso yesitshixo yangaphandle ecacileyo ngale ndawo.

#2: isalathisi sendlela (i-BitmapAnd)

Nini

Bonisa zonke izivumelwano zomthengi "LLC Kolokolchik" ezigqityiweyo egameni le "NJSC Lyutik".

Indlela yokuchonga

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

Iingcebiso

Yenza isalathiso esiyintlanganisela ngamasimi avela kwimithombo yomibini okanye ukwandisa enye yemihlaba ekhoyo ukusuka kweyesibini.

Umzekelo:

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); -- отбор по конкретной паре

[Jonga apha explain.tensor.ru]

Siyalungisa:

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

[Jonga apha explain.tensor.ru]

Apha inzuzo incinci, kuba iBitmap Heap Scan isebenza ngokwayo. Kodwa kunjalo 7x ngokukhawuleza kwaye 2.5x ukufundwa okumbalwa.

#3: Ukudibanisa izalathisi (BitmapOr)

Nini

Bonisa izicelo zokuqala ezingama-20 "ezakhe" okanye ezingabiwanga ukuze ziqwalaselwe, ezizezakhe eziphambili.

Indlela yokuchonga

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

Iingcebiso

Sebenzisa IMANYANO [ZONKE] ukudibanisa subqueries kwimeko nganye OKANYE iibhloko.

Umzekelo:

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;

[Jonga apha explain.tensor.ru]

Siyalungisa:

(
  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, больше и не надо

[Jonga apha explain.tensor.ru]

Sithathe ithuba lokuba zonke iirekhodi ezingama-20 eziyimfuneko zafunyanwa kwangoko kwibhloko yokuqala, ke eyesibini, neyona “ibizi kakhulu” i-Bitmap Heap Scan, ayizange iphunyezwe - ngenxa yoko. 22x ngokukhawuleza, 44x ukufunda okumbalwa!

Ibali elineenkcukacha malunga nale ndlela yokuphucula kwimizekelo ebonakalayo inokufundwa kumanqaku I-PostgreSQL Antipatterns: I-JOIN enobungozi kunye ne-ORs и I-PostgreSQL Antipatterns: Ibali lokuCwangciswa okuPhindayo kokuKhangela ngeGama, okanye "Ukuphucula emva naphambili".

Uguqulelo oluqhelekileyo ukhetho olucwangcisiweyo ngamaqhosha amaninzi (kwaye hayi nje ipere ye const / NULL) kuxoxwa ngayo kwinqaku SQL HowTo: bhala ixesha-loop ngokuthe ngqo kumbuzo, okanye "Elementary three-way".

#4: Sifunda kakhulu

Nini

Njengomthetho, kwenzeka xa ufuna "ukuncamathisela esinye isihluzi" kwisicelo esele sikhona.

“Kwaye awunayo into efanayo, kodwa ngamaqhosha eperile? » ifilimu "Isandla seDiamond"

Umzekelo, ukulungisa umsebenzi ongasentla, bonisa izicelo zokuqala ezingama-20 "ezibalulekileyo" zokuqhubekeka, nokuba ziyintoni na injongo yazo.

Indlela yokuchonga

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

Iingcebiso

Yenza [okungakumbi] okukhethekileyo isalathiso esinegatya apho okanye uquke imimandla eyongezelelweyo kwisalathiso.

Ukuba imeko yokucoca "i-static" kwimisebenzi yakho - oko kukuthi alubandakanyi ukwandiswa uluhlu lwamaxabiso kwixesha elizayo - kungcono ukusebenzisa isalathisi apho. Iimo ezahlukeneyo ze-boolean/enum zingena kakuhle kolu didi.

Ukuba imeko yokucoca inokuthatha amaxabiso ahlukeneyo, kungcono ukwandisa isalathisi kunye nale mihlaba - njengakwimeko nge-BitmapAnd ngasentla.

Umzekelo:

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;

[Jonga apha explain.tensor.ru]

Siyalungisa:

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

[Jonga apha explain.tensor.ru]

Njengoko ubona, ukuhluzwa kwisicwangciso kuphelile ngokupheleleyo, kwaye isicelo siye saba Amaxesha ama-5 ngokukhawuleza.

#5: itafile enqabileyo

Nini

Iinzame ezahlukeneyo zokwenza umgca wokucwangcisa umsebenzi wakho, xa inani elikhulu lohlaziyo / ukususwa kweerekhodi kwitafile kukhokelela kwimeko yenani elikhulu leerekhodi "ezifileyo".

Indlela yokuchonga

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

Iingcebiso

Yenza ngesandla rhoqo IVACUUM [EGCWELE] okanye ukuphumeza ngokwaneleyo ukusetyenzwa rhoqo i-autovacuum ngokulungelelanisa iiparamitha zayo, kuquka kwitafile ethile.

Kwiimeko ezininzi, iingxaki ezinjalo zibangelwa kukubekwa kwemibuzo engalunganga xa kubizwa ingqiqo yeshishini, njengaleyo kuxoxwe ngayo kuyo I-PostgreSQL Antipatterns: imikhosi yokulwa "yabafileyo".

Kodwa kufuneka siqonde ukuba ne-VACUUM FULL ayinakuhlala inceda. Kwiimeko ezinjalo, kufuneka uziqhelanise ne-algorithm evela kwinqaku. I-DBA: xa i-VACUUM idlula, sicoca itafile ngesandla.

#6: ukufunda ukusuka "kumbindi" wesalathiso

Nini

Kubonakala ngathi bafunde kancinci, kwaye yonke into yayifakwe kwisalathiso, kwaye abazange bahluze nabani na ngokungaphezulu-kodwa kunjalo, amaphepha amaninzi afundwe kunokuba singathanda.

Indlela yokuchonga

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

Iingcebiso

Thatha ujongo olusondeleyo kulwakhiwo lwesalathiso esisetyenzisiweyo kunye nemimandla ephambili ekhankanyiweyo kumbuzo - kakhulu kunokwenzeka, inxalenye yesalathiso ayimiselwanga. Kuya kufuneka wenze isalathiso esifanayo, kodwa ngaphandle kwemihlaba yesimaphambili, okanye bafunde ukuphindaphinda iinqobo zabo.

Umzekelo:

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;

[Jonga apha explain.tensor.ru]

Yonke into ibonakala ilungile, nangokwemigaqo yesalathiso, kodwa ngandlela-thile iyakrokra - kwirekhodi nganye ye-20 efundwayo, amaphepha e-4 edatha kwafuneka athatyathwe, i-32KB ngerekhodi - ayinesibindi? Ewe kunye negama lesalathisi tbl_fk_org_fk_cli_idx ikhokelela kwingcinga.

Siyalungisa:

CREATE INDEX ON tbl(fk_cli);

[Jonga apha explain.tensor.ru]

Ngequbuliso - Amaxesha angama-10 ngokukhawuleza kunye namaxesha ama-4 ngaphantsi ukufunda!

Ngeminye imizekelo yokusetyenziswa ngokungafanelekanga kwezalathisi, bona inqaku I-DBA: fumana izalathisi ezingenamsebenzi.

#7: CTE × CTE

Nini

Ngesicelo amanqaku "amafutha" CTE ukusuka kwiitafile ezahlukeneyo, kwaye emva koko wagqiba ukwenza phakathi kwabo JOIN.

Ityala lifanelekile kwiinguqulelo ezingezantsi kwe-v12 okanye izicelo nge WITH MATERIALIZED.

Indlela yokuchonga

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

Iingcebiso

Sihlalutye isicelo ngononophelo zifuneka ii-CTEs apha kwaphela? Ukuba ewe, ngoko sebenzisa "isichazi-magama" kwi-hstore/json ngokwemodeli echazwe kwi PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN.

#8: tshintshela kwidisk (ixesha libhaliwe)

Nini

Ukusetyenzwa kwexesha elinye (ukuhlelwa okanye ukwahlulahlula) inani elikhulu leerekhodi alingeni kwimemori eyabelwe oku.

Indlela yokuchonga

-> *
   && temp written > 0

Iingcebiso

Ukuba inani lememori elisetyenziswe ngumsebenzi aligqithisi kakhulu ixabiso elimiselweyo leparameter umsebenzi_mem, ifanele ilungiswe. Ungakhawuleza kuqwalaselo lomntu wonke, okanye ungadlula SET [LOCAL] ngesicelo esithile/intengiselwano.

Umzekelo:

SHOW work_mem;
-- "16MB"

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

[Jonga apha explain.tensor.ru]

Siyalungisa:

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

[Jonga apha explain.tensor.ru]

Ngenxa yezizathu ezicacileyo, ukuba imemori kuphela isetyenzisiweyo kwaye kungekhona idiski, ngoko umbuzo uya kukhawuleza kakhulu. Ngexesha elifanayo, inxalenye yomthwalo iphinda isuswe kwi-HDD.

Kodwa kufuneka uqonde ukuba ukwaba imemori eninzi akusayi kuhlala kusebenze - akuyi kwanela wonke umntu.

#9: Amanani angasebenziyo

Nini

Ininzi yagalelwa kwisiseko kwangoko, kodwa babengenaxesha lokuyigxotha ANALYZE.

Indlela yokuchonga

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

Iingcebiso

Chitha okufanayo ANALYZE.

Le meko ichazwe ngokubanzi kwi I-PostgreSQL Antipatterns: izibalo ziyintloko yento yonke.

#10: "ikhona into engalunganga"

Nini

Kwakukho isitshixo esilinde isicelo esikhuphisanayo, okanye kwakungekho zixhobo zaneleyo ze-CPU/hypervisor hardware.

Indlela yokuchonga

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

Iingcebiso

Sebenzisa yangaphandle inkqubo yokubeka iliso umncedisi wokuthintela okanye ukusetyenziswa kobutyebi ngokungaqhelekanga. Sele sithethile malunga nenguqulelo yethu yokulungiselela le nkqubo kumakhulu amaseva. apha и apha.

umthombo: www.habr.com