SeqScan из не вакуумированной таблицы

Пришла такая мысль — проверить как отразится на обращение к таблице с помощью seqscan наличией в ней большого количества «мертвых» строк. Ответ у меня есть, но решил его практически првоерить.

Кратко получилось вот так:

Подготовка:

Создаем таблицу сразу с отключенным для неё процессом автоочистки:

CREATE TABLE tabv (txt text) WITH (autovacuum_enabled = off);

Про автоочистку можно почитать, например, тут: «Хабр. MVCC-7. Автоочистка».

Вставляем туда миллион строк следующим образом:

INSERT INTO tabv SELECT (999^99*id)::text FROM generate_series(1,1000000) id;

На метод хранения столбца в этот раз обращать внимания не будем, всё по умолчанию.

SET max_parallel_workers_per_gather = 0;

Уберем этой настройкой процесс создания параллельных исполнителей — в данном случае они только отвелкать будут.

Эксперимент:

Выполняем запрос, который приведет к последовательному сканированию всей таблицы:

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) from tabv;

Смотрим план, обращаем внимание на BUFFERS — это число страниц, которое пришлось прочитать при работе. Очень важный показатель работы сервера:

                                                     QUERY PLAN                                                     
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=17199.00..17199.01 rows=1 width=8) (actual time=167.463..167.465 rows=1 loops=1)
   Buffers: shared hit=6370
   ->  Seq Scan on tabv  (cost=0.00..15033.20 rows=866320 width=0) (actual time=0.048..97.770 rows=1000000 loops=1)
         Buffers: shared hit=6370
 Planning Time: 0.135 ms
 Execution Time: 167.538 ms
(6 rows)

Time: 168,232 ms

Можно несколько раз запрос выполнить чтобы значение BUFFERS стабилизировалось на одном значении — 6370.

Посмотрим размер таблицы:

\dt+ tabv
                           List of relations
  Schema  | Name | Type  |  Owner   | Persistence | Size  | Description 
----------+------+-------+----------+-------------+-------+-------------
 bookings | tabv | table | postgres | permanent   | 50 MB |

А теперь обновим весь этот миллион строк, но сделаем это в отдельной транзакции:

BEGIN;
UPDATE tabv set txt = (999^99*id)::text FROM generate_series(1,1) id;

И в той же транзакции повторим EXPLAIN который был выше:

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) from tabv;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=34395.30..34395.31 rows=1 width=8) (actual time=198.620..198.622 rows=1 loops=1)
   Buffers: shared hit=12739
   ->  Seq Scan on tabv  (cost=0.00..30064.04 rows=1732504 width=0) (actual time=67.911..148.882 rows=1000000 loops=1)
         Buffers: shared hit=12739
 Planning Time: 0.097 ms
 Execution Time: 198.677 ms
(6 rows)

Time: 199,192 ms

И снова можно несколько раз выполнить, чтобы BUFFERS стал постоянным. Смотрим на значением — 12739. Очень интересное значение, оно (почти) ровно в два раза выше чем предыдущее.

Обратите внимание на значение rows — миллион. Так как миллион видимых строк, а еще миллион мертвых в плане не отображается.

Посмотрим размер таблицы:

\dt+ tabv
                            List of relations
  Schema  | Name | Type  |  Owner   | Persistence |  Size  | Description 
----------+------+-------+----------+-------------+--------+-------------
 bookings | tabv | table | postgres | permanent   | 100 MB | 

Опять обновим миллион строк:

UPDATE tabv set txt = (999^99*id)::text FROM generate_series(1,1) id;

И выполним тот же запрос:

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) from tabv;

                                                       QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=51594.30..51594.31 rows=1 width=8) (actual time=224.956..224.957 rows=1 loops=1)
   Buffers: shared hit=16352 read=2757
   ->  Seq Scan on tabv  (cost=0.00..45097.24 rows=2598824 width=0) (actual time=112.414..181.315 rows=1000000 loops=1)
         Buffers: shared hit=16352 read=2757
 Planning Time: 0.052 ms
 Execution Time: 224.992 ms
(6 rows)

Time: 225,320 ms

Снова смотрим размер таблицы:

\dt+ tabv
                            List of relations
  Schema  | Name | Type  |  Owner   | Persistence |  Size  | Description 
----------+------+-------+----------+-------------+--------+-------------
 bookings | tabv | table | postgres | permanent   | 149 MB | 

В этот раз не вот такое вот получилось с BUFFERS. Но ничего страшного, суммируем и получаем 19109 — в три раза больше, чем наше первоначальное значение BUFFERS. И, раз автовакуум не чистит нашу таблицу — тогда у нас стало в три раза больше строк в таблице (хотя актуальных, по прежнему, один миллион).

А теперь отменяем транзакцию:

ROLLBACK;

И снова запускаем тот же запрос выше:

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) from tabv;

                                                      QUERY PLAN                                                      
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=51594.30..51594.31 rows=1 width=8) (actual time=187.201..187.203 rows=1 loops=1)
   Buffers: shared hit=16280 read=2829
   ->  Seq Scan on tabv  (cost=0.00..45097.24 rows=2598824 width=0) (actual time=0.033..112.328 rows=1000000 loops=1)
         Buffers: shared hit=16280 read=2829
 Planning Time: 0.095 ms
 Execution Time: 187.259 ms
(6 rows)

Time: 187,873 ms

И снова в BUFFERS прошлое (максимальное) значение 19109. Как так? Транзакцию же отменили, всё успешно откатилось?

Кстати, размер таблицы — тот же, максимальный:

\dt+ tabv
                            List of relations
  Schema  | Name | Type  |  Owner   | Persistence |  Size  | Description 
----------+------+-------+----------+-------------+--------+-------------
 bookings | tabv | table | postgres | permanent   | 149 MB | 

Ответ кратко — потому что PostgreSQL использует версионирование (MVCC): во время обновления строк (UPDATE) помечает их как удаленные и создает новые, с новыми значениями. А помеченные как удаленные строки не удаляются сразу — их удаляет специальный процесс (AUTO)VACUUM, который мы для этой таблицы отключили.

Ответ подробнее — в курсе DBA2. Настройка и мониторинг.

Добьем эксперимент — запустим процесс очистки руками и повторим запрос:

VACUUM tabv;

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) from tabv;
                                                     QUERY PLAN                                                      
-----------------------------------------------------------------------------------------------------------
 Aggregate  (cost=18870.00..18870.01 rows=1 width=8) (actual time=153.674..153.675 rows=1 loops=1)
   Buffers: shared hit=3850 read=2520
   ->  Seq Scan on tabv  (cost=0.00..16370.00 rows=1000000 width=0) (actual time=0.019..90.680 rows=1000000 loops=1)
         Buffers: shared hit=3850 read=2520
 Planning Time: 0.068 ms
 Execution Time: 153.718 ms
(6 rows)

Time: 154,229 ms

В BUFFERS опять те же 6370, которые были изначально.

Глянем размер таблицы напоследок:

\dt+ tabv
                           List of relations
  Schema  | Name | Type  |  Owner   | Persistence | Size  | Description 
----------+------+-------+----------+-------------+-------+-------------
 bookings | tabv | table | postgres | permanent   | 50 MB |

Опять в три раза меньше, как и было в самом начале. Пусть будет так.

Еще разок таблица с результатами:

Тут и на время можно обратить внимание, оно тоже увеличивается.

Так что не забывайте про правильные настройки автовакуума.