Предисловие
В эпоху, когда нейросети становятся первым источником знаний для многих разработчиков, особенно важно проверять их утверждения на практике. Один из таких вопросов — прямая связь между типами ожиданий в PostgreSQL и отсутствием индексов. AI-помощники часто дают логичные, но упрощённые ответы, которые могут ввести в заблуждение при решении реальных задач оптимизации. В этой статье мы экспериментально, с помощью инструмента pg_expecto, проверим , насколько обоснованно распространённое мнение о том, что IO-ожидания однозначно указывают на проблемы с индексацией.
ℹ️Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub
kznalp/PG_EXPECTO: Комплекс статистического анализа производительности СУБД PostgreSQL
Вопрос
Являются ли ожидания типа IO необходимым и достаточным условием отсутствия индекса для таблицы или материализованного представления в СУБД PostgreSQL ?
ℹ️Ответ нейросети Ask Postgres
В PostgreSQL ожидание типа IO (ввод-вывод) не является ни необходимым, ни достаточным условием для отсутствия индекса на таблице или материализованном представлении.
Пояснение:
1. Что такое ожидание IO?
Ожидание IO — это состояние, при котором процесс СУБД ожидает завершения операций чтения/записи на диске (например, чтение страницы данных с диска в буферный пул). Это метрика производительности, отражающая задержки, связанные с физическим доступом к данным, а не структурную особенность таблицы.
2. Связь с индексами
Наличие или отсутствие индекса — это логическая структура, определяемая при создании таблицы или явно через CREATE INDEX.
Ожидание IO возникает, когда PostgreSQL не может найти нужные данные в оперативной памяти (буферном пуле) и должен прочитать их с диска — независимо от наличия индекса.Без индекса: PostgreSQL может выполнять полное сканирование таблицы (Seq Scan), что приводит к большим IO-ожиданиям.
С индексом: PostgreSQL может выполнять индексный сканирование (Index Scan), но если индекс не покрывает запрос, он всё равно может обращаться к таблице (Index + Heap Scan), что также вызывает IO.
3. Необходимость и достаточность
Не является необходимым: Таблица без индекса может не вызывать IO-ожиданий, если все данные уже в буферном пуле (например, после нескольких повторных запросов).
Не является достаточным: Наличие IO-ожиданий не означает отсутствие индекса — индексированные запросы тоже могут вызывать IO, если индекс или данные не кэшированы.
4. Вывод
Ожидание IO — это результат выполнения запроса, а не причина или признак отсутствия индекса.
Отсутствие индекса может способствовать увеличению IO-ожиданий, но не является единственной причиной, и наличие IO-ожиданий не доказывает отсутствие индекса.
Итог: Ожидание типа IO — независимый параметр производительности, не связанный логически с наличием индекса как с необходимым или достаточным условием.
См. документацию: [PostgreSQL Performance Monitoring] — раздел о pg_stat_activity и pg_stat_statements.
?️♂️Экспериментальная проверка необходимого и достаточного условия отсутствия индекса
Исходные данные: инцидент производительности СУБД
ℹ️SQL запросы, вызывающие 80% ожиданий типа IO - 43 запроса
PG_EXPECTO : Диаграмма Парето по ожиданиям SQL запросов | Postgres DBA | Дзен
Вопрос по ожиданиям IO
❓Как среди списка SQL запросов - определить запросы для которых в таблицах не хватает индексов ?
Гипотеза
Используя, результаты ранее сделанных экспериментов :
PG_HAZEL : ожидания СУБД PostgreSQL при отсутствии индексов. | Postgres DBA | Дзен
Использование метода доступа Seq Scan | Parallel Seq Scan совместно с использованием параллельных процессов( Workers Planned ,Workers Launched ) в плане выполнения запросов, обнаруженных в ходе корреляционного анализа.
...
ℹ️При возникновении данных условий могут возникнуть массовые ожидания IPC/BgWorkerShutdown.
Можно сделать предположение:
?Подтверждающим признаком, сужающим круг поиска SQL запросов, для оптимизации которых, необходимо создать индексы в таблицах - является ожидание BgWorkerShutdown.
Проверка гипотезы
ℹ️SQL запросы, вызывающие 80% ожиданий типа IPC
PG_EXPECTO : Диаграмма Парето по ожиданиям SQL запросов | Postgres DBA | Дзен
⚠️SQL запросы, вызывающие ожидания типа IO и IPС :
?Проведенный анализ таблиц, участвующих в запросах, вызывающих ожидания BgWorkerShutdown и DSMFillZeroWrite, показал отсутствие индексов по столбцам, используемым в условиях запросов.
ВЫВОД
?Одновременная корреляция ожидания IPC/BgWorkerShutdown и IO/DSMFillZeroWrite может служить надежным признаком необходимости добавления индексов для таблиц, участвующих в запросах, выявленных в ходе анализа инцидента производительности СУБД, значительно сужая область оптимизации по ожиданиям IO.
Экспериментальное подтверждение гипотезы
ожидания типа IO не являются ни необходимым, ни достаточным условием отсутствия индекса.
1. Почему это не необходимое условие?
Необходимое условие означает: "Если индекса нет, то мы обязательно увидим IO ожидания".
Это неверно. Отсутствие индекса может проявляться другими типами ожиданий, или вообще не проявляться в мониторинге ожиданий в конкретный момент времени.
Единственный запрос, вызывающий ожидания IPC, но не вызывающий ожидания IO: queryid = 3449463017331132112
Текст SQL запроса
SELECT * FROM "Table1" WHERE "Table1"."Col2" IN (X1)"Table1" является материализованным представлением
Materialized view "public.Table1"
Column | Type | Collation | Nullable | Default
-------+--------+-----------+----------+--------
Col1 | bigint | | |
Col2 | bigint | | |
Col3 | bigint | | |
Indexes:
"Table1_Col1_idx" btree ("Col1")
"Table1_Col1_uniq_idx" UNIQUE, btree ("Col1")⚠️Индекс по столбцу Col2 отсутствует , но ожиданий IO - нет.
2. Почему это не достаточное условие?
Достаточное условие означает: "Если мы видим IO ожидания, то это гарантированно означает, что не хватает индекса".
Это тоже неверно. IO-ожидания — это очень общий симптом, который указывает на то, что СУБД много читает с диска. Причин для этого может быть множество, и отсутствие индекса — лишь одна из них.
SQL запрос, вызывающий ожидания IO
Текст SQL запроса
SELECT
"Table1"."Col1",
"Table1"."Col2",
"Table1"."Col3",
"Table1"."Col4",
"Table1"."Col5",
"Table1"."Col6",
"Table1"."Col7",
"Table1"."Col8",
"Table1"."Col9",
"Table2"."Col1" AS "Table2.Col1",
"Table2"."Col10" AS "Table2.Col10",
"Table2"."Col11" AS "Table2.Col11",
"Table2"."Col12" AS "Table2.Col12",
"Table3"."Col13" AS "Table3.Col13",
"Table3"."Col14" AS "Table3.Col14",
"Table3"."Col15" AS "Table3.Col15",
"Table3"."Col16" AS "Table3.Col16",
"Table3"."Col17" AS "Table3.Col17",
"Table3"."Col18" AS "Table3.Col_18",
"Table3"."Col19" AS "Table3.Col_19",
"Table3"."Col20" AS "Table3.Col_20",
"Table3"."Col21" AS "Table3.Col_21",
"Table4"."Col1" AS "Table4.Col1", "Table4->Table5"."Col1" AS "Table4.Table5.Col1",
"Table4->Table5"."Col22" AS "Table4.Table5.Col22",
"Table4->Table5"."Col23" AS "Table4.Table5.Col23"
FROM
"public"."Table1" AS "Table1"
LEFT OUTER JOIN "public"."Table2" AS "Table2" ON "Table1"."Table2Col1" = "Table2"."Col1"
LEFT OUTER JOIN "public"."Table3" AS "Table3" ON "Table1"."Col1" = "Table3"."Col14"
INNER JOIN "public"."Table4" AS "Table4" ON "Table1"."Table4Col1" = "Table4"."Col1"
LEFT OUTER JOIN "public"."Table5" AS "Table4->Table5" ON "Table4"."Table5Col1" = "Table4->Table5"."Col1"
WHERE
"Table1"."Col24" IS NULL AND
"Table1"."Col1" > X1
ORDER BY "Table1"."Col1" ASC LIMIT L1План выполнения
Limit (cost=2.17..70.52 rows=L1 wCol1th=173)
-> Nested Loop Left Join (cost=2.17..856461.37 rows=2505886 wCol1th=173)
-> Nested Loop (cost=1.73..528761.08 rows=2505886 wCol1th=160)
-> Merge Left Join (cost=1.29..304507.29 rows=2505886 wCol1th=152)
Merge Cond: ("Table1".Col1 = "Table3".Col14)
-> Nested Loop Left Join (cost=0.86..208037.72 rows=2505886 wCol1th=80)
-> Index Scan using "Table1_pkey" on "Table1" "Table1" (cost=0.43..139011.91 rows=2505886 wCol1th=68)
Index Cond: (Col1 > X1)
Filter: ("Col24" IS NULL)
-> Memoize (cost=0.43..0.45 rows=1 wCol1th=20)
Cache Key: "Table1"."Table2Col1"
Cache Mode: logical
-> Index Scan using "Table2_pkey" on "Table2" (cost=0.42..0.44 rows=1 wCol1th=20)
Index Cond: (Col1 = "Table1"."Table2Col1")
-> Index Scan using "Col1x_N_Col14" on Table3 "Table3" (cost=0.43..70410.89 rows=2375831 wCol1th=72)
-> Memoize (cost=0.43..0.47 rows=1 wCol1th=16)
Cache Key: "Table1"."Table4Col1"
Cache Mode: logical
-> Index Scan using "Table4_pkey" on "Table4" Table4 (cost=0.42..0.46 rows=1 wCol1th=16)
Index Cond: (Col1 = "Table1"."Table4Col1")
-> Memoize (cost=0.44..0.55 rows=1 wCol1th=21)
Cache Key: Table4."Table5Col1"
Cache Mode: logical
-> Index Scan using "Table5_pkey" on "Table5" "Table4->Table5" (cost=0.43..0.54 rows=1 wCol1th=21)
Index Cond: (Col1 = Table4."Table5Col1")
(25 rows)⚠️Метод доступа - только Index Scan, но при выполнении запроса возникают ожидания IO.
Вывод
ℹ️Наличие ожиданий ввода-вывода (IO) при выполнении SQL-запросов не служит однозначным индикатором потребности в создании индексов для задействованных в запросе таблиц.