Несколько месяцев назад — публичный к PostgreSQL.
За прошедшее время вы уже воспользовались им более 6000 раз, но одна из удобных функций могла остаться незамеченной — это структурные подсказки, которые выглядят примерно так:
Прислушивайтесь к ним, и ваши запросы «станут гладкими и шелковистыми». 🙂
А если серьезно, то многие ситуации, которые делают запрос медленным и «прожорливым» по ресурсам, типичны и могут быть распознаны по структуре и данным плана.
В этом случае каждому отдельному разработчику не придется искать вариант оптимизации самостоятельно, опираясь исключительно на свой опыт — мы можем ему подсказать, что тут происходит, в чем может быть причина, и как можно подойти к решению. Что мы и сделали.
Давайте чуть подробнее рассмотрим эти кейсы — как они определяются и к каким рекомендациям приводят.
Для лучшего погружения в тему сначала можно послушать соответствующий блок из , а уже потом перейти к детальному разбору каждого примера:
#1: индексная «недосортировка»
Когда возникает
Показать последний счет по клиенту «ООО Колокольчик».
Как опознать
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
R rekomandimet
Используемый индекс расширить полями сортировки.
Shembuj:
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 ScanR rekomandimet
Krijo составной индекс по полям из обоих исходных или расширить один из существующих полями из второго.
Shembuj:
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 ScanR rekomandimet
Përdorni UNION [ALL] для объединения подзапросов по каждому из OR-блоков условий.
Shembuj:
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, даже не выполнялся — в итоге в 22 раза быстрее, в 44 раза меньше чтений!
Более детальный рассказ о данном способе оптимизации на конкретных примерах можно прочитать в статьях dhe .
Обобщенный вариант упорядоченного отбора по нескольким ключам (а не только по паре const/NULL) рассмотрен в статье .
#4: читаем много лишнего
Когда возникает
Как правило, возникает при желании «прикрутить еще один фильтр» к уже существующему запросу.
«А у вас нет такого же, но с перламутровыми пуговицами?» х/ф «Бриллиантовая рука»
Например, модифицируя задачу выше, показать первые 20 самых старых «критичных» заявок для обработки, независимо от их назначенности.
Как опознать
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
R rekomandimet
Создать [более] специализированный индекс с WHERE-условием или включить в индекс дополнительные поля.
Если условие фильтрации является «статичным» для ваших задач — то есть не предполагает расширения перечня значений в будущем — лучше использовать WHERE-индекс. В эту категорию хорошо укладываются разные boolean/enum-статусы.
Если же условие фильтрации может принимать разные значения, то лучше расширить индекс этими полями — как в ситуации с BitmapAnd выше.
Shembuj:
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
R rekomandimet
Регулярно вручную проводить VACUUM [FULL] или добиться адекватно частой отработки с помощью тонкой настройки его параметров, в том числе .
В большинстве случаев подобные проблемы оказываются вызваны плохой компоновкой запросов при вызовах с бизнес-логики вроде тех, которые были рассмотрены в .
Но надо понимать, что даже VACUUM FULL может помочь не всегда. Для таких случаев стоит ознакомиться с алгоритмом из статьи .
#6: чтение с «середины» индекса
Когда возникает
Вроде и прочитали немного, и все по индексу, и никого лишнего не фильтровали — а все равно прочитано существенно больше страниц, чем хотелось бы.
Как опознать
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
R rekomandimet
Внимательно посмотреть на структуру использовавшегося индекса и ключевые поля, заданные в запросе — скорее всего, часть индекса не задана. Скорее всего, вам придется создать похожий индекс, но без префиксных полей или .
Shembuj:
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 на запись — не жирно ли? Да и имя индекса 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
R rekomandimet
Внимательно проанализировать запрос — а ? Если все-таки да, то применить «ословаривание» в hstore/json по модели, описанной в .
#8: swap на диск (temp written)
Когда возникает
Разовая обработка (сортировка или уникализация) большого количества записей не влезает в выделенную для этого память.
Как опознать
-> *
&& temp written > 0R rekomandimet
Если использованное операцией количество памяти не сильно превышает установленное значение параметра , стоит его скорректировать. Можно сразу в конфиге для всех, а можно через SET [LOCAL] для конкретного запроса/транзакции.
Shembuj:
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 >> 10R rekomandimet
Провести-таки ANALYZE.
Подробнее данная ситуация расписана в .
#10: «что-то пошло не так»
Когда возникает
Случилось ожидание блокировки, наложенной конкурирующим запросом, или не хватило аппаратных ресурсов CPU/гипервизора.
Как опознать
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
R rekomandimet
Используйте внешнюю систему для мониторинга сервера на предмет наличия блокировок или нештатного потребления ресурсов. Про наш вариант организации этого процесса для сотен серверов мы уже рассказывали dhe .
Burimi: habr.com
