Diversi xhur ilu - pubbliku għal PostgreSQL.
Diġà użajha aktar minn 6000 darba minn dakinhar, iżda waħda mill-karatteristiċi utli setgħet ma tkunx innotata hija ħjiel strutturali, li jidhru xi ħaġa bħal din:
Ismagħhom u t-talbiet tiegħek "isiru bla xkiel tal-ħarir". 🙂
Iżda serjament, ħafna sitwazzjonijiet li jagħmlu talba bil-mod u "gluttonous" f'termini ta 'riżorsi, huma tipiċi u jistgħu jiġu rikonoxxuti mill-istruttura u d-dejta tal-pjan.
F'dan il-każ, kull żviluppatur individwali mhux se jkollu jfittex għażla ta' ottimizzazzjoni waħdu, billi jiddependi biss fuq l-esperjenza tiegħu stess - nistgħu ngħidulu x'qed jiġri hawn, x'tista' tkun ir-raġuni, u kif toħroġ b'soluzzjoni. Li huwa dak li għamilna.
Ejja nagħtu ħarsa aktar mill-qrib lejn dawn il-każijiet - kif huma definiti u għal liema rakkomandazzjonijiet iwasslu.
Għal immersjoni aħjar fis-suġġett, l-ewwel tista 'tisma' l-blokk korrispondenti minn , u mbagħad biss mur għal analiżi dettaljata ta 'kull eżempju:
#1: indiċi "undersorting"
Meta tqum
Uri l-aħħar fattura għall-klijent "LLC Kolokolchik".
Kif tidentifika
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Rakkomandazzjonijiet
Indiċi użat jespandu bl-oqsma sort.
Eżempju:
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;
Tista 'tinnota immedjatament li aktar minn 100 rekord tnaqqsu mill-indiċi, li mbagħad ġew kollha magħżula, u mbagħad baqa' l-uniku wieħed.
Aħna nirranġaw:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Anke fuq kampjun primittiv bħal dan - 8.5x aktar mgħaġġel u 33x inqas qari. L-effett se jkun aktar ċar, aktar ikollok "fatti" għal kull valur. fk.
Ninnota li indiċi bħal dan se jaħdem bħala indiċi "prefiss" mhux agħar minn dak preċedenti għal mistoqsijiet oħra b' fk, fejn issortjar minn pk ma kienx u mhuwiex (tista 'taqra aktar dwar dan ). B'mod partikolari, se jipprovdi normali appoġġ espliċitu taċ-ċavetta barranija minn dan il-qasam.
#2: intersezzjoni tal-indiċi (BitmapAnd)
Meta tqum
Uri l-kuntratti kollha għall-klijent "LLC Kolokolchik" konkluż f'isem "NJSC Lyutik".
Kif tidentifika
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRakkomandazzjonijiet
joħolqu indiċi kompost minn oqsma miż-żewġ sorsi jew jespandu wieħed mill-oqsma eżistenti mit-tieni.
Eżempju:
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); -- отбор по конкретной паре
Aħna nirranġaw:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Hawnhekk il-qligħ huwa iżgħar, peress li Bitmap Heap Scan huwa pjuttost effettiv waħdu. Imma xorta waħda 7x aktar mgħaġġel u 2.5x inqas qari.
#3: Tgħaqqad l-Indiċi (BitmapOr)
Meta tqum
Uri l-ewwel 20 eqdem talba "proprja" jew mhux assenjata għall-ipproċessar, b'prijorità proprja.
Kif tidentifika
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRakkomandazzjonijiet
Uża UNJONI [KOLLHA] biex tgħaqqad subqueries għal kull waħda mill-kondizzjoni JEW blokki.
Eżempju:
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;
Aħna nirranġaw:
(
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, больше и не надо
Ħadna vantaġġ mill-fatt li l-20 rekord meħtieġ inkisbu immedjatament fl-ewwel blokk, għalhekk it-tieni wieħed, bl-aktar "għali" Bitmap Heap Scan, lanqas biss ġie eżegwit - bħala riżultat. 22x aktar mgħaġġel, 44x inqas qari!
Storja aktar dettaljata dwar dan il-metodu ta 'ottimizzazzjoni fuq eżempji konkreti jistgħu jinqraw fl-artikoli и .
Verżjoni ġeneralizzata għażla ordnata minn diversi ċwievet (u mhux biss għall-par const/NULL) huwa diskuss fl-artiklu .
#4: Naqraw wisq
Meta tqum
Bħala regola, dan iseħħ meta trid "tehmeż filtru ieħor" ma 'talba eżistenti.
“U m’għandekx l-istess, imma bil-buttuni tal-perla? " film "Diamond Hand"
Pereżempju, billi timmodifika l-kompitu t'hawn fuq, uri l-ewwel 20 talba "kritika" l-eqdem għall-ipproċessar, irrispettivament mill-iskop tagħhom.
Kif tidentifika
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Rakkomandazzjonijiet
Oħloq [aktar] speċjalizzat indiċi bi klawżola FEJN jew inkludi oqsma addizzjonali fl-indiċi.
Jekk il-kundizzjoni tal-filtrazzjoni hija "statika" għall-kompiti tiegħek - jiġifieri ma jinkludix espansjoni lista ta 'valuri fil-futur - huwa aħjar li tuża indiċi FEJN. Diversi status boolean/enum jidħlu tajjeb f'din il-kategorija.
Jekk il-kundizzjoni tal-filtrazzjoni jistgħu jieħdu valuri differenti, huwa aħjar li tespandi l-indiċi b'dawn l-oqsma - bħal fis-sitwazzjoni ma 'BitmapAnd hawn fuq.
Eżempju:
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;
Aħna nirranġaw:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Kif tistgħu taraw, il-filtrazzjoni mill-pjan spiċċa kompletament, u t-talba saret 5 darbiet aktar mgħaġġla.
#5: tabella skarsa
Meta tqum
Diversi tentattivi biex tagħmel il-kju tal-ipproċessar tal-kompiti tiegħek stess, meta numru kbir ta 'aġġornamenti / tħassir ta' rekords fuq il-mejda jwasslu għal sitwazzjoni ta 'numru kbir ta' rekords "mejtin".
Kif tidentifika
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Rakkomandazzjonijiet
Manwalment twettaq regolarment VAKWU [SĦIĦA] jew tikseb proċessar frekwenti adegwatament billi tirfina l-parametri tagħha, inkluż .
Fil-biċċa l-kbira tal-każijiet, problemi bħal dawn huma kkawżati minn tqassim fqir ta’ mistoqsija meta msejħa mill-loġika tan-negozju, bħal dawk diskussi f’ .
Imma rridu nifhmu li anke VACUUM FULL mhux dejjem jista 'jgħin. Għal każijiet bħal dawn, għandek tiffamiljarizza ruħek mal-algoritmu mill-artiklu. .
#6: qari min-"nofs" tal-indiċi
Meta tqum
Jidher li qraw ftit, u kollox kien indiċjat, u ma ffiltraw lil ħadd żejjed - iżda xorta waħda, inqraw b'mod sinifikanti aktar paġni milli nixtiequ.
Kif tidentifika
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Rakkomandazzjonijiet
Agħti ħarsa mill-qrib lejn l-istruttura tal-indiċi użat u l-oqsma ewlenin speċifikati fil-mistoqsija - x'aktarx, parti indiċi mhux issettjata. X'aktarx ikollok bżonn toħloq indiċi simili, iżda mingħajr oqsma ta' prefiss, jew .
Eżempju:
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;
Jidher li kollox huwa tajjeb, anke f'termini tal-indiċi, iżda b'xi mod suspettuż - għal kull wieħed mill-20 rekord li jinqraw, kellhom jitnaqqsu 4 paġni ta 'data, 32KB għal kull rekord - mhux grassa? Iva u isem indiċi tbl_fk_org_fk_cli_idx iwassal għall-ħsieb.
Aħna nirranġaw:
CREATE INDEX ON tbl(fk_cli);
F'daqqa waħda - 10 darbiet aktar mgħaġġla u 4 darbiet inqas biex taqra!
Għal aktar eżempji ta 'użu ineffiċjenti ta' indiċi, ara l-artiklu .
#7: CTE × CTE
Meta tqum
Fit-talba skurja "xaħam" CTE minn tabelli differenti, u mbagħad iddeċieda li jagħmel bejniethom JOIN.
Il-każ huwa rilevanti għall-verżjonijiet taħt v12 jew talbiet ma WITH MATERIALIZED.
Kif tidentifika
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Rakkomandazzjonijiet
Analizza t-talba bir-reqqa ? Jekk iva, allura applika "dizzjunarju" f'hstore/json skond il-mudell deskritt fil .
#8: tpartit ma' disk (temp bil-miktub)
Meta tqum
L-ipproċessar ta 'darba (l-għażla jew l-unikalizzazzjoni) ta' numru kbir ta 'rekords ma jidħolx fil-memorja allokata għal dan.
Kif tidentifika
-> *
&& temp written > 0Rakkomandazzjonijiet
Jekk l-ammont ta 'memorja użata mill-operazzjoni ma jaqbiżx ħafna l-valur stabbilit tal-parametru , għandu jiġi kkoreġut. Tista 'minnufih fil-konfigurazzjoni għal kulħadd, jew tista' permezz SET [LOCAL] għal talba/transazzjoni speċifika.
Eżempju:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Aħna nirranġaw:
SET work_mem = '128MB'; -- перед выполнением запроса
Għal raġunijiet ovvji, jekk tintuża biss il-memorja u mhux id-disk, allura l-mistoqsija tkun ħafna aktar mgħaġġla. Fl-istess ħin, parti mit-tagħbija titneħħa wkoll mill-HDD.
Imma rridu nifhmu li mhux dejjem se taħdem ukoll li talloka ħafna memorja - hija cory mhux biżżejjed għal kulħadd.
#9: Statistika irrilevanti
Meta tqum
Ħafna tferra 'fil-bażi f'daqqa, iżda ma kellhomx ħin biex ikeċċuh ANALYZE.
Kif tidentifika
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Rakkomandazzjonijiet
Onfoq l-istess ANALYZE.
Din is-sitwazzjoni hija deskritta f'aktar dettall fi .
#10: "xi ħaġa marret ħażin"
Meta tqum
Kien hemm lock tistenna talba li tikkompeti, jew ma kienx hemm biżżejjed riżorsi ta 'hardware CPU/hypervisor.
Kif tidentifika
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Rakkomandazzjonijiet
Uża esterna sistema ta’ monitoraġġ server għall-imblukkar jew konsum anormali tar-riżorsi. Diġà tkellimna dwar il-verżjoni tagħna tal-organizzazzjoni ta 'dan il-proċess għal mijiet ta' servers. и .
Sors: www.habr.com