PG_HAZEL : ожидания СУБД PostgreSQL при отсутствии индексов.

Заранее известно - гепард быстрее, но гораздо интереснее - почему? А еще интереснее - как помочь улитке стать такой же быстрой.

Начало , версия СУБД , конфигурация ВМ, тестовый запрос

Задача эксперимента

Определение и анализ характерных ожиданий, вызванных отсутствием индекса при массовых операциях SELECT+UPDATE. Определение причин возникновения ожиданий СУБД при выполнении операций SELECT+UPDATE без использования индексов.

Сравнительные эксперименты

Эксперимент-1 : Без использования индекса.

Эксперимент-2 : С использованием индекса.

Операционная скорость и медианное время тестового SQL запроса

Сравнительная таблица показателей скорости и медианного времени.

Результат

Разница значений скорости и медианного времени между экспериментами - на порядки.

Корреляция между типами ожиданий и ожиданиями СУБД

Сравнительная таблица коэфициентов корреляции по эксперименту-1 и эксперименту-2

Результат

• Коэффициент корреляции между операционной скоростью и ожиданиями СУБД практически не изменился.
• В эксперименте-2 отсутствует корреляция по типам ожидания IO и IPC.

Корреляция между типом ожидания и событиями ожидания при выполнении тестового запроса

Эксперимент-1 : Тип ожидания "IO"

События ожидания по типу ожидания IO

Наибольшая корреляция по событию ожидания DataFileRead.

Эксперимент-1 : Тип ожидания "IPC"

События ожидания по типу ожидания IO

Наибольшая корреляция по событиям ожидания BufferIO , BgWorkerShutdown.

Важная деталь по ожиданиям типа IO/IPC

Количество событий ожидания по типу IPC существенно выше, чем по событий ожидания по типу IO.

Эксперимент-1,Эксперимент-2 : Тип ожидания "Lock"

События ожидания по типу Lock

• Событие ожидания relation отсутствует в Эксперименте-1.
• Количество ожиданий - одного порядка.

Эксперимент-1,Эксперимент-2 : Тип ожидания "LWLock"

События ожиданий по типу LWLock

• Событие ожидания BufferMapping отсутствует в Эксперименте-2
• Событие ожидания SyncScan отсутствует в Эксперименте-2
• Событие ожидания ParallelQuieryDSA отсутствует в Эксперименте-2
• Количество событий ожидания LockManager в Эксперименте-2 существенно больше. Причина - кардинальный рост скорости СУБД в Эксперименте-2.
• Количество событий ожидания BufferContent в Эксперименте-2 существенно больше. Причина - кардинальный рост скорости СУБД в Эксперименте-2.
• Количество событий ожидания ProcArray в Эксперименте-2 существенно больше. Причина - кардинальный рост скорости СУБД в Эксперименте-2.

Планы выполнения тестового запроса

Эксперимент-1

UPDATE pgbench_accounts SET abalance = abalance + 11 WHERE aid = 1000000;

Update on pgbench_accounts (cost=0.00..57786.99 rows=0 width=0) (actual time=259.768..259.770 rows=0 loops=1)

Buffers: shared hit=420 read=32370

I/O Timings: shared read=101.848

-> Seq Scan on pgbench_accounts (cost=0.00..57786.99 rows=1 width=10) (actual time=259.718..259.718 rows=1 loops=1)

Filter: (aid = 1000000)

Rows Removed by Filter: 1999999

Buffers: shared hit=417 read=32370

I/O Timings: shared read=101.848

SELECT abalance FROM pgbench_accounts WHERE aid = 1000000;

Gather (cost=1000.00..40037.10 rows=1 width=4) (actual time=153.680..160.046 rows=1 loops=1)

Workers Planned: 4

Workers Launched: 4

Buffers: shared hit=1089 read=31698

I/O Timings: shared read=213.323

-> Parallel Seq Scan on pgbench_accounts (cost=0.00..39037.00 rows=1 width=4) (actual time=128.613..128.614 rows=0 loops=5)

Filter: (aid = 1000000)

Rows Removed by Filter: 400000

Buffers: shared hit=1089 read=31698

I/O Timings: shared read=213.323

Эксперимент-2

UPDATE pgbench_accounts SET abalance = abalance + 11 WHERE aid = 1000000;

Update on pgbench_accounts (cost=0.43..2.65 rows=0 width=0) (actual time=0.066..0.066 rows=0 loops=1)

Buffers: shared hit=7

-> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.43..2.65 rows=1 width=10) (actual time=0.024..0.024 rows=1 loops=1)

Index Cond: (aid = 1000000)

Buffers: shared hit=4

SELECT abalance FROM pgbench_accounts WHERE aid = 1000000;

Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.43..2.65 rows=1 width=4) (actual time=0.034..0.035 rows=1 loops=1)

Index Cond: (aid = 1000000)

Buffers: shared hit=4

Итог и результаты анализа

Характерными признаками необходимости создания индекса для SQL запроса, при выполнении массовых операций SELECT+UPDATE являются:

1. Сильная корреляция ожиданий СУБД с типами ожидания IO/IPC
2. Существенное превышение количества ожиданий типа IPC над количеством ожиданий типа IO.
3. Использование метода доступа Seq Scan | Parallel Seq Scan совместно с использованием параллельных процессов( Workers Planned ,Workers Launched ) в плане выполнения запросов, обнаруженных в ходе корреляционного анализа.

Дополнение о параллельности при выполнении SQL запросов:

ℹ️Условия возникновения параллельных процессов в плане выполнения SQL запроса:

5. Типы сканирования таблиц:  

  - Поддерживаются следующие виды сканирования таблицы, рассчитанные на параллельное выполнение:  

   - ⚠️Параллельное последовательное сканирование: блоки таблицы будут разделены на диапазоны, распределяемые между взаимодействующими процессами.⚠️  

   - Параллельное сканирование кучи по битовой карте: один процесс выбирается на роль ведущего, который производит сканирование одного или нескольких индексов и строит битовую карту.  

   - Параллельное сканирование по индексу или параллельное сканирование только индекса: взаимодействующие процессы читают данные из индекса по очереди.  

  6. Параллельные соединения:  

  - Внутренняя сторона соединения может быть любым видом непараллельного плана, который в остальном поддерживается планировщиком, при условии, что он безопасен для выполнения в параллельном исполнителе.  

  - При соединении по хешу (параллельном соединении по хешу) внутреннее соединение выполняется параллельно, что позволяет избежать дублирования работы по построению хеш-таблицы.  

📝Цитата из ответа ChatPPG на вопрос о причинах возникновения параллельных процессов в плане выполнения.

ℹ️При возникновении данных условий могут возникнуть массовые ожидания IPC/BgWorkerShutdown.