Преди няколко месеца — публичен към PostgreSQL.
През изминалото време вече сте го използвали над 6000 пъти, но една от удобните функции може да е останала незабелязана — това са структурни подсказки, които изглеждат приблизително така:
Вслушайте се в тях и вашите заявки ще станат „гладки и копринени“. 🙂
А ако говорим сериозно, много ситуации, които правят заявката бавна и „гладна“ за ресурси, са типични и могат да бъдат разпознати по структурата и данните на плана.
В този случай на всеки отделен разработчик не му се налага да търси вариант за оптимизация самостоятелно, разчитайки единствено на своя опит — можем да му подсскажем, какво се случва, каква може да е причината и как може да се подходи към решението. Това и направихме.
Нека разгледаме по-подробно тези случаи — как те се определят и до какви препоръки водят.
За по-добро потапяне в темата, първо можете да изслушате съответния блок от , а след това да преминете към детайлния анализ на всеки пример:
#1: индексная «недосортировка»
Кога възниква
Показване на последния отчет за клиента „ООО Колокольчик“.
Как да разпознаем
-> Limit
-> Sort
-> индекс [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 нямат и не са имали (повече информация можете да прочетете ). Включително, той ще осигури и нормална поддръжка на явния foreign key по това поле.
#2: пересечение индексов (BitmapAnd)
Кога възниква
Показване на всички договори с клиента „ООО Колокольчик“, сключени от името на „НАО Лютик“.
Как да разпознаем
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanПрепоръки
Създайте композитен индекс по полета от и двата източника или разширете един от съществуващите с полета от втория.
Пример:
СЪЗДАЙ ТАБЛИЦА tbl КАТО
ИЗБЕРИ
generate_series(1, 100000) pk -- 100K "факта"
, (random() * 100)::integer fk_org -- 100 различни външни ключа
, (random() * 1000)::integer fk_cli; -- 1K различни външни ключа
СЪЗДАЙ ИНДЕКС върху tbl(fk_org); -- индекс за външен ключ
СЪЗДАЙ ИНДЕКС върху tbl(fk_cli); -- индекс за външен ключ
ИЗБЕРИ
*
ОТ
tbl
КЪДЕ
(fk_org, fk_cli) = (1, 999); -- отбор по конкретна двойка 
Коригирано:
ИЗТРИЙ ИНДЕКС tbl_fk_org_idx;
СЪЗДАЙ ИНДЕКС върху tbl(fk_org, fk_cli);
Тук печалбата е по-малка, защото Bitmap Heap Scan е достатъчно ефективен сам по себе си. Но все пак в 7 пъти по-бързо и с 2.5 пъти по-малко четения.
#3: объединение индексов (BitmapOr)
Кога възниква
Показването на първите 20 най-стари „свои“ или неназначени заявки за обработка, като приоритет имат своите.
Как да разпознаем
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanПрепоръки
Използвайте UNION [ALL] за комбиниране на подзаявки за всяка от OR-блоковете на условията.
Пример:
СЪЗДАЙ ТАБЛИЦА tbl КАТО
ИЗБЕРИ
generate_series(1, 100000) pk -- 100K "факта"
, СЛУЧАЙ
КОГАТО random() < 1::real/16 ТОГАВА NULL -- с вероятност 1:16 запис "ничи"
ИНАЧЕ (random() * 100)::integer -- 100 различни външни ключа
КРАЙ fk_own;
СЪЗДАЙ ИНДЕКС върху tbl(fk_own, pk); -- индекс с "мнозина подходящо" подреждане
ИЗБЕРИ
*
ОТ
tbl
КЪДЕ
fk_own = 1 ИЛИ -- свои
fk_own IS NULL -- ... или "ничи"
НАЧИН НА
pk
, (fk_own = 1) DESC -- първо "свои"
LIMIT 20;
Коригирано:
(
ИЗБЕРИ
*
ОТ
tbl
КЪДЕ
fk_own = 1 -- първо „свои“ 20
НАЧИН НА
pk
LIMIT 20
)
СЪЮЗ ВСИЧКИ
(
ИЗБЕРИ
*
ОТ
tbl
КЪДЕ
fk_own IS NULL -- после "ничи" 20
НАЧИН НА
pk
LIMIT 20
)
LIMIT 20; -- но всичко - 20, повече не е нужно 
Ние се възползвахме от това, че всички 20 нужни записа бяха получени веднага в първия блок, затова вторият, с по-„скъпия“ Bitmap Heap Scan, даже не се изпълни - в крайна сметка в 22 пъти по-бързо, с 44 пъти по-малко четения!
По-подробно разказ за този метод на оптимизация на конкретни примери може да се прочете в статиите и .
Обобщен вариант подреден отбор по множество ключове (а не само по двойка const/NULL) е разгледан в статията .
#4: читаем много лишнего
Кога възниква
Обикновено възниква при желание за „добавяне на още един филтър“ към вече съществуваща заявка.
„А нямате ли такъв, но с перлени копчета?» филм „Брилянтовата ръка“
Например, модифицирайки задачата по-горе, да покажем първите 20 най-стари „критични“ заявки за обработка, независимо от тяхната назначеност.
Как да разпознаем
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × редове 80% прочетени
&& цикли × RRbF > 100 -- и при това над 100 записа общо
Препоръки
Създайте [по-] специализиран индекс с WHERE-условие или включете допълнителни полета в индекса.
Ако условието за филтриране е "статично" за вашите задачи — тоест не предполага разширяване на списъка с стойности в бъдеще — е по-добре да се използва WHERE-индекс. В тази категория добре попадат различни boolean/enum-статуси.
Ако обаче условието за филтриране може да приема различни стойности, е по-добре да разширите индекса с тези полета — както в ситуацията с BitmapAnd по-горе.
Пример:
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]
&& цикли × (редове + RRbF) 64
Препоръки
Редовно ръчно извършвайте VACUUM [FULL] или постигнете адекватно често изпълнение на чрез внимателно настройване на неговите параметри, включително .
В повечето случаи подобни проблеми се дължат на лоша компилация на заявките при извиквания с бизнес-логика, като онези, които бяха разгледани в .
Но трябва да се разбере, че дори VACUUM FULL не винаги помага. За такива случаи си струва да се запознаете с алгоритъма от статията .
#6: чтение с «середины» индекса
Кога възниква
Изглежда, че сме прочели малко, всичко е по индекса, и не сме филтрирали нищо излишно — а все пак е прочетена значително по-голяма сума страници, отколкото бихме искали.
Как да разпознаем
-> Index [Only] Scan [Backward]
&& цикли × (редове + RRbF) 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 страници данни, 32KB на записа — не е ли прекалено? И името на индекса таблица_fk_org_fk_cli_idx наводнява мислите.
Коригирано:
CREATE INDEX ON tbl(fk_cli); 
Неочаквано — 10 пъти по-бързо и 4 пъти по-малко прочетено!
Други примери за неефективно използване на индексите можете да видите в статията .
#7: CTE × CTE
Кога възниква
В заявката набрали "едри" CTE от различни таблици, а след това решили да направят между тях JOIN.
Случаят е актуален за версии под v12 или за заявки с WITH MATERIALIZED.
Как да разпознаем
-> CTE Scan
&& цикли > 10
&& цикли × (редове + RRbF) > 10000
-- твърде голямо декартово произведение CTE
Препоръки
Внимателно анализирайте заявката — а ? Если все-таки да, то приложете "осложняването" в hstore/json по модела, описан в .
#8: swap на диск (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]
&& отношение >> 10Препоръки
Трябва да се проведе ANALYZE.
Подробности относно тази ситуация са описани в .
#10: «что-то пошло не так»
Кога възниква
Случи се изчакване на блокировка, наложена от конкурентна заявка, или недостиг на хардуерни ресурси CPU/хипервизор.
Как да разпознаем
-> *
&& (споделено хит / 8K) + (споделено четене / 1K) < времето / 1000
-- RAM хит = 64MB/s, HDD четене = 8MB/s
&& времето > 100ms -- четяхме малко, но твърде дълго
Препоръки
Използвайте външна система за мониторинг на сървъри за наличие на блокировки или неочаквано потребление на ресурси. За нашия вариант за организиране на този процес за стотици сървъри вече сме говорили и .
Източник: habr.com
