Пред неколку месеци - јавност до 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: раскрсница на индекси (BitmapAnd)
Кога ќе се појави
Прикажи ги сите договори за клиентот „ДОО Колоколчик“, склучени во име на „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: Спојување на индекси (BitmapOr)
Кога ќе се појави
Прикажи ги првите 20 најстари „нас“ или недоделени барања за обработка, со твоето во приоритет.
Како да се идентификуваат
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanПрепораки
Користете УНИЈА [СИТЕ] да се комбинираат подпрашања за секој од ИЛИ-блоковите на услови.
Пример:
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 барани записи беа веднаш примени во првиот блок, па вториот, со по „скапото“ Bitmap Heap Scan, не беше ни извршен - на крајот 22x побрзо, 44x помалку читања!
Подетална приказна за овој метод на оптимизација користејќи конкретни примери може да се прочита во статии и .
Генерализирана верзија нарачан избор врз основа на неколку клучеви (и не само парот const/NULL) се дискутира во статијата .
#4: Читаме многу непотребни работи
Кога ќе се појави
Како по правило, се појавува кога сакате да „прикачите друг филтер“ на веќе постоечко барање.
„И вие го немате истиот, но со копчиња од мајка на бисер? " филмот „Дијамантската рака“
На пример, менувајќи ја задачата погоре, прикажете ги првите 20 најстари „критични“ барања за обработка, без оглед на нивната намена.
Како да се идентификуваат
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Препораки
Направете [повеќе] специјализирани индекс со услов WHERE или вклучи дополнителни полиња во индексот.
Ако состојбата на филтерот е „статична“ за вашите цели - тоа е не подразбира проширување список на вредности во иднина - подобро е да се користи индекс WHERE. Различни булонски/енум статуси добро се вклопуваат во оваа категорија.
Ако состојбата на филтрирање може да добие различни значења, тогаш подобро е да го проширите индексот со овие полиња - како во ситуацијата со 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]
&& 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 KB по запис - не е тоа смело? И името на индексот tbl_fk_org_fk_cli_idx провоцирачки.
Исправување:
CREATE INDEX ON tbl(fk_cli);
Одеднаш - 10 пати побрзо и 4 пати помалку за читање!
Други примери на ситуации на неефективна употреба на индекси може да се видат во статијата .
#7: CTE × 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: „нешто тргна наопаку“
Кога ќе се појави
Се чекаше за заклучување наметнато од конкурентно барање или немаше доволно хардверски ресурси на процесорот/хипервизорот.
Како да се идентификуваат
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Препораки
Користете надворешно систем за следење сервер за блокирање или ненормална потрошувачка на ресурси. Веќе разговаравме за нашата верзија на организирање на овој процес за стотици сервери и .
Извор: www.habr.com