Якчанд моҳ пеш - ҷамъиятӣ ба PostgreSQL.
Шумо онро аллакай зиёда аз 6000 маротиба истифода кардаед, аммо як хусусияти муфид, ки шояд нодида гирифта шавад нишондодҳои сохторӣ, ки чизе монанди ин аст:
Ба онҳо гӯш диҳед, ва дархостҳои шумо "ҳамвор ва абрешим хоҳанд шуд". 🙂
Аммо ба таври ҷиддӣ, бисёр ҳолатҳо, ки дархостро суст ва ба захираҳо гурусна мекунанд хосанд ва аз руи структура ва маълумотхои план эътироф карда мешаванд.
Дар ин ҳолат, ҳар як таҳиягари инфиродӣ набояд мустақилона як варианти оптимизатсияро ҷустуҷӯ кунад ва танҳо ба таҷрибаи худ такя кунад - мо метавонем ба ӯ бигӯем, ки дар ин ҷо чӣ рӯй дода истодааст, сабаб чист ва ба ҳалли масъала чӣ гуна муносибат кардан мумкин аст. Мо ин корро кардем.
Биёед ба ин ҳолатҳо бодиққат назар кунем - онҳо чӣ гуна муайян карда мешаванд ва онҳо ба кадом тавсияҳо оварда мерасонанд.
Барои беҳтар ғарқ шудан ба мавзӯъ, шумо метавонед аввал блоки мувофиқро аз он гӯш кунед , ва танҳо пас ба таҳлили муфассали ҳар як мисол гузаред:
№1: индекси "камсоркунӣ"
Вақте ки ба миён меояд
Ҳисобнома-фактураи охирини муштарии "ҶДММ Колоколчик" -ро нишон диҳед.
Чӣ тавр муайян кардан
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
тавсияњои
Индекс истифода мешавад бо майдонҳои ҷудокунӣ васеъ кунед.
Мисол:
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;
Шумо дарҳол мушоҳида карда метавонед, ки аз индекс зиёда аз 100 сабт хориҷ карда шуданд, ки баъд ҳама ба тартиб оварда шуданд ва баъд танҳо як сабт боқӣ монд.
Ислоҳ:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Ҳатто дар чунин намунаи ибтидоӣ - 8.5 маротиба тезтар ва 33 маротиба камтар хондан. Чӣ қадаре ки шумо барои ҳар як арзиш бештар "далелҳо" дошта бошед, таъсир ҳамон қадар равшантар мешавад fk.
Ман қайд мекунам, ки чунин индекс ҳамчун индекси "префикс" кор хоҳад кард, на аз пештара барои дархостҳои дигар бо fk, дар куҷо ҷудо кунед pk набуд ва вуҷуд надорад (шумо метавонед дар ин бора бештар хонед ). Аз ҷумла, он муқаррариро таъмин хоҳад кард дастгирии возеҳи калиди хориҷӣ дар ин майдон.
# 2: чорроҳаи индекс (BimapAnd)
Вақте ки ба миён меояд
Ҳама шартномаҳоро барои муштарии ҶДММ «Колоколчик», ки аз номи «NAO Buttercup» баста шудааст, нишон диҳед.
Чӣ тавр муайян кардан
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanтавсияњои
Эҷод кунед индекси таркиби аз рӯи майдонҳо аз ҳарду майдонҳои аслӣ ё яке аз майдонҳои мавҷударо бо майдонҳои дуюм васеъ кунед.
Мисол:
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); -- отбор по конкретной паре
Ислоҳ:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Мукофот дар ин ҷо камтар аст, зеро Bitmap Heap Scan мустақилона хеле самаранок аст. Аммо ба ҳар ҳол 7 маротиба тезтар ва 2.5 маротиба камтар хондан.
№3: Индексҳоро якҷоя кунед (BimapOr)
Вақте ки ба миён меояд
Аввалин 20 "мо"-и қадимтарин ё дархостҳои таъиннашударо барои коркард бо афзалияти шумо нишон диҳед.
Чӣ тавр муайян кардан
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanтавсияњои
Барои истифода ИТТИФОКИ [ХАМА] барои якҷоя кардани зерпурсишҳо барои ҳар яке аз OR-блокҳои шартҳо.
Мисол:
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;
Ислоҳ:
(
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, больше и не надо
Мо аз он истифода бурдем, ки ҳамаи 20 сабти зарурӣ фавран дар блоки аввал гирифта шуданд, бинобар ин, дуюм, бо "гаронтар" Scan Heap Bitmap, ҳатто иҷро карда нашуд - дар ниҳоят 22 маротиба тезтар, 44 маротиба камтар хондан!
Ҳикояи муфассал дар бораи ин усули оптимизатсия бо истифода аз мисолҳои мушаххас дар маколахо хондан мумкин аст и .
Версияи умумӣ интихоби фармоиш дар асоси якчанд калидҳои (ва на танҳо ҷуфти const/NULL) дар мақола муҳокима карда мешавад .
# 4: Мо бисёр чизҳои нолозимро мехонем
Вақте ки ба миён меояд
Чун қоида, он вақте ба миён меояд, ки шумо мехоҳед ба дархости мавҷуда "филтри дигарро замима кунед".
"Ва шумо якхела надоред, аммо бо тугмаҳои марворидОё филми "Бозуи алмос"
Масалан, тағир додани вазифаи дар боло зикршуда, новобаста аз ҳадафи онҳо, аввалин 20 дархости қадимтарини "интиқодӣ" -ро барои коркард нишон диҳед.
Чӣ тавр муайян кардан
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
тавсияњои
Эҷоди [бештар] махсусгардонидашуда индекс бо ҳолати WHERE ё майдонҳои иловагиро ба индекс дохил кунед.
Агар ҳолати филтр барои мақсадҳои шумо "статикӣ" бошад - яъне васеъ карданро дар назар надорад рӯйхати арзишҳо дар оянда - беҳтар аст, ки индекси WHERE -ро истифода баред. Ҳолатҳои гуногуни логикӣ/enum ба ин категория мувофиқат мекунанд.
Агар ҳолати филтркунӣ метавонад маънои гуногун дошта бошад, пас беҳтар аст, ки шохисро бо ин майдонҳо васеъ кунед - ба монанди ҳолати BitmapВа дар боло.
Мисол:
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;
Ислоҳ:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Тавре ки шумо мебинед, филтркунӣ аз нақша комилан аз байн рафтааст ва дархост шудааст 5 маротиба тезтар.
№ 5: мизи камёфт
Вақте ки ба миён меояд
Кӯшишҳои гуногун барои сохтани навбати коркарди вазифаҳои шахсии худ, вақте ки шумораи зиёди навсозӣ/несткунии сабтҳо дар ҷадвал боиси вазъияти шумораи зиёди сабтҳои "мурда" мешаванд.
Чӣ тавр муайян кардан
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
тавсияњои
Мунтазам дастӣ иҷро кунед ВАКУУМ [ПУРРА] ё ба таври кофӣ зуд-зуд машқ кардан бо рохи дуруст ба рох мондани параметрхои он, аз чумла .
Дар аксари мавридҳо, чунин мушкилот аз сабаби таркиби сусти дархостҳо ҳангоми занг задан аз мантиқи тиҷорӣ, ба монанди онҳое, ки дар он баррасӣ шудаанд, ба вуҷуд меоянд .
Аммо шумо бояд фаҳмед, ки ҳатто VACUUM FULL на ҳамеша кӯмак мекунад. Барои чунин ҳолатҳо, шумо бояд бо алгоритми мақола шинос шавед .
№6: Хондан аз "миёна"-и шохис
Вақте ки ба миён меояд
Чунин ба назар мерасад, ки мо каме хондем ва ҳама чиз индексатсия карда шуд ва мо ҳеҷ касро аз ҳад зиёд филтр накардем - аммо ба ҳар ҳол мо саҳифаҳои зиёдеро аз он ки мехостем, мехонем.
Чӣ тавр муайян кардан
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
тавсияњои
Ба сохтори индекси истифодашуда ва майдонҳои калидии дар дархост нишондодашуда бодиққат назар кунед - эҳтимоли зиёд як кисми индекс мукаррар карда нашудааст. Эҳтимол дорад, ки шумо бояд як шохиси шабеҳ эҷод кунед, аммо бидуни майдонҳои префикс ё .
Мисол:
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;
Ба назар чунин мерасад, ки ҳама чиз хуб аст, ҳатто аз рӯи индекс, аммо он то андозае шубҳанок аст - барои ҳар як аз 20 сабти хондашуда, мо бояд 4 саҳифаи маълумот, 32 КБ барои як сабтро хориҷ кунем - оё ин ғафс нест? Ва номи индекс tbl_fk_org_fk_cli_idx ба андеша водоркунанда.
Ислоҳ:
CREATE INDEX ON tbl(fk_cli);
Ногаҳон - 10 маротиба тезтар ва 4 маротиба камтар хондан!
Намунаҳои дигари ҳолатҳои истифодаи бесамари индексҳоро дар мақола дидан мумкин аст .
№ 7: CTE × CTE
Вақте ки ба миён меояд
Дар дархост КТЭ-и «фарбеҳ» ба даст овард аз мизҳои гуногун, ва он гоҳ қарор кард, ки ин корро дар байни онҳо JOIN.
Парвандаи мазкур барои версияҳои поёнии v12 ё дархостҳои бо WITH MATERIALIZED.
Чӣ тавр муайян кардан
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
тавсияњои
Бодиққат дархостро таҳлил кунед - ва ? Агар ҳа, пас "Луғат"-ро дар hstore/json татбиқ кунед мувофиқи модели дар тавсифшуда .
# 8: иваз кардан ба диск (муваффақият навишта шудааст)
Вақте ки ба миён меояд
Коркарди якдафъаина (ҷудокунӣ ё ягонакунии) миқдори зиёди сабтҳо ба хотираи барои ин ҷудошуда мувофиқат намекунад.
Чӣ тавр муайян кардан
-> *
&& temp written > 0тавсияњои
Агар микдори хотираи истифодашавандаи амалиёт аз арзиши муайяншудаи параметр зиёд набошад , ислох кардан меарзад. Шумо метавонед фавран дар конфигуратсия барои ҳама, ё шумо метавонед тавассути SET [LOCAL] барои дархост/амали мушаххас.
Мисол:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Ислоҳ:
SET work_mem = '128MB'; -- перед выполнением запроса
Бо сабабҳои маълум, агар танҳо хотира истифода шавад, на диск, он гоҳ дархост хеле тезтар иҷро карда мешавад. Дар айни замон, як қисми сарборӣ аз HDD низ хориҷ карда мешавад.
Аммо шумо бояд фаҳмед, ки шумо на ҳама вақт хотираи зиёдеро ҷудо карда наметавонед - он барои ҳама кофӣ нахоҳад буд.
№9: омори номарбуте
Вақте ки ба миён меояд
Онҳо якбора ба базаи маълумот бисёр рехтанд, аммо барои дур кардани он вақт надоштанд ANALYZE.
Чӣ тавр муайян кардан
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10тавсияњои
Иҷро кунед ANALYZE.
Ин вазъият дар муфассалтар тасвир шудааст .
# 10: "чизе хато рафт"
Вақте ки ба миён меояд
Интизорӣ барои қулф аз ҷониби дархости рақобаткунанда вуҷуд дошт ё захираҳои сахтафзори CPU/hypervisor нокифоя буданд.
Чӣ тавр муайян кардан
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
тавсияњои
Истифодаи беруна системаи мониторинг сервер барои масдуд кардан ё истеъмоли ғайримуқаррарии захираҳо. Мо аллакай дар бораи версияи ташкили ин раванд барои садҳо серверҳо сӯҳбат кардем и .
Манбаъ: will.com