Update
explain ( analyze , buffers )
UPDATE pgbench_accounts
SET abalance = abalance + 100
WHERE aid = 5000;
Планы выполнения
QUERY PLAN - 1
Update on pgbench_accounts (cost=0.57..2.79 rows=0 width=0) (actual time=0.550..0.551 rows=0 loops=1)
Buffers: shared hit=12 dirtied=2
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..2.79 rows=1 width=10) (actual time=0.132..0.134 rows=1 loops=1)
Index Cond: (aid = 5000)
Buffers: shared hit=5
Planning:
Buffers: shared hit=34
Planning Time: 0.370 ms
Execution Time: 0.702 ms
(9 rows)
QUERY PLAN-2
Update on pgbench_accounts (cost=0.57..2.79 rows=0 width=0) (actual time=0.117..0.117 rows=0 loops=1)
Buffers: shared hit=8
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..2.79 rows=1 width=10) (actual time=0.068..0.069 rows=1 loops=1)
Index Cond: (aid = 5000)
Buffers: shared hit=5
Planning Time: 0.111 ms
Execution Time: 0.160 ms
(7 rows)
QUERY PLAN-3
Update on pgbench_accounts (cost=0.57..2.79 rows=0 width=0) (actual time=0.099..0.100 rows=0 loops=1)
Buffers: shared hit=8
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.57..2.79 rows=1 width=10) (actual time=0.045..0.046 rows=1 loops=1)
Index Cond: (aid = 5000)
Buffers: shared hit=5
Planning Time: 0.116 ms
Execution Time: 0.153 ms
(7 rows)
Анализ - RTFM
План выполнения показывает три последовательных выполнения одного и того же UPDATE. Различие в dirtied=2 только при первом выполнении обусловлено работой механизма буферизации PostgreSQL и особенностями кэширования. Вот подробный анализ:
1. Что означает dirtied?
- dirtied=X — количество блоков данных (страниц), которые были изменены в памяти (буферном кэше) и помечены как «грязные». Эти блоки должны быть записаны на диск при следующем checkpoint или вытеснении из кэша.
- shared hit — блоки, уже находящиеся в буферном кэше (чтение из памяти).
2. Анализ трёх выполнений:
Первое выполнение:
Buffers: shared hit=12 dirtied=2
- Общее обращений к буферам: 12.
- Изменено («загрязнено»): 2 блока.
- Что произошло:
- Index Scan прочитал индекс и нашёл запись (aid = 5000), загрузив блоки индекса и таблицы в кэш (shared hit=5).
- UPDATE модифицировал:
- Блок данных таблицы, где хранится строка с aid=5000.
- Блок журнала транзакций (WAL) или системный блок (например, блок метаданных в таблице или индексной странице, если потребовалась физическая модификация индекса).
- Два блока были изменены в памяти и помечены как dirtied.
Второе и третье выполнения:
Buffers: shared hit=8 // и shared hit=8 соответственно
- Нет dirtied — ни один блок не был помечен как «грязный».
- Причина: те же самые блоки (данных и индекса) уже находились в буферном кэше и уже были помечены как «грязные» после первого UPDATE. Они ожидают записи на диск.
- Последующие UPDATE только обновляют значения в этих же блоках, но статус «грязный» не меняется (блоки остаются «грязными» до сброса на диск).
- Почему shared hit=8, а не 12?
- Часть служебных блоков (например, WAL-буферы) могли остаться в кэше, но не требовали повторной обработки. Также PostgreSQL мог оптимизировать внутренние операции (например, не перечитывать некоторые метаданные).
3. Почему dirtied только в первый раз?
- Повторные UPDATE не создают новых «грязных» блоков — они перезаписывают уже изменённые данные в тех же блоках.
- Механизм WAL (Write-Ahead Logging) гарантирует, что изменения записываются в журнал транзакций, но сами блоки данных в кэше остаются «грязными» до checkpoint.
- Если бы между выполнениями произошёл checkpoint, «грязные» блоки были бы сброшены на диск, и последующий UPDATE снова показал бы dirtied.
4. Различия в производительности:
- Первое выполнение: Execution Time = 0.702 ms
- Дополнительное время на маркировку блоков как «грязных», возможная инициализация структур в памяти.
- Второе/третье выполнения: ~0.15–0.16 ms
- Только изменение данных в уже загруженных и помеченных блоках.
5. Практический вывод:
- Повторные UPDATE одной строки выполняются быстрее (нет накладных расходов на «загрязнение» новых блоков).
- Нагрузка на ввод-вывод возникает при первом изменении блока или когда «грязные» блоки сбрасываются на диск.
- Если в таблице есть HOT (Heap-Only Tuples)-обновления, dirtied может затрагивать только блок таблицы, а не индексы (если изменяются только непроиндексированные столбцы).
Итог:
dirtied=2 только при первом UPDATE, потому что PostgreSQL помечает блоки как «грязные» при первом изменении после загрузки в кэш. Последующие изменения тех же блоков не меняют их статус — они остаются «грязными» до записи на диск. Это нормальное поведение, оптимизирующее работу с памятью и диском.