Комментарии 5
приятно читать статью с качественным примером
Почему вакуум так "не дорабатывает"? Возможно, это предмет для следующей публикации.
Анонимный блок работал в одной транзакции. Если транзакция единственная, то автовакуум не вакуумировал таблицу, так как накопительная статистика публикуется после завершения транзакции. Если бы даже автовакуум ее вакуумировал, то не смог бы ничего очистить, так транзакция удерживала горизонт очистки базы данных.
Пока транзакция работала, таблица разрослась в 100 раз. После выполнения анонимного блока, транзакция зафиксировалась, автовакуум отработал и успешно очистил старые версии строк, но файл таблицы уже разросся до 17160блоков, которые и будут сканироваться при Sec Scan и, при использовании Sec Scan, запрос будет выполняться дольше.
Проблема решается добавлением commit; перед end loop;
К сожалению, не решается. Пока весь DO не отработает, статистика не обновится (даже если внутри много транзакций, а не одна).
да, лучше обновлять не в одном блоке. Без обновления накопительной статистики, если другие сессии не меняли таблицу, то автовакуум не будет отрабатывать, пока весь DO не отработает.
Однако, даже в этом примере без вакуума индекс станет в 10 раз меньше (1144 kB вместо 14 MB) и сканирование по индексу станет быстрее полного сканирования:
postgres=# do
$$
begin
for i in 1..300 loop
with cte as (select u_id from test_idxscan limit 5000)
update test_idxscan set u_id=u_id where u_id in (select u_id from cte);
end loop;
end$$;
DO
Time: 108458.838 ms (01:48.459)
postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and p_id=2;
QUERY PLAN
---------------------------------------------------------------------------------------------
Seq Scan on test_idxscan (cost=0.00..17310.00 rows=100 width=61) (actual rows=100 loops=1)
Filter: ((domain_id = 0) AND (p_id = 2))
Rows Removed by Filter: 9900
Buffers: shared hit=17160
Planning Time: 0.059 ms
Execution Time: 8.139 ms
(6 rows)
postgres=# set enable_seqscan = off;
SET
postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and p_id=2;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Scan using pk_test_idxscan on test_idxscan (cost=0.41..38237.34 rows=100 width=61) (actual rows=100 loops=1)
Index Cond: (domain_id = 0)
Filter: (p_id = 2)
Rows Removed by Filter: 9900
Buffers: shared hit=11675
Planning Time: 0.062 ms
Execution Time: 7.340 ms
(7 rows)
postgres=# \dt+ test_idxscan
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+--------+-------------
public | test_idxscan | table | postgres | permanent | heap | 134 MB |
(1 row)
postgres=# \di+ pk_test_idxscan
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+-----------------+-------+----------+--------------+-------------+---------------+-------+-------------
public | pk_test_idxscan | index | postgres | test_idxscan | permanent | btree | 14 MB |
(1 row)
========================
postgres=# do
$$
begin
for i in 1..300 loop
with cte as (select u_id from test_idxscan limit 5000)
update test_idxscan set u_id=u_id where u_id in (select u_id from cte);
commit; end loop;
end$$;
DO
Time: 16853.510 ms (00:16.854)
postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and p_id=2;
QUERY PLAN
---------------------------------------------------------------------------------------------
Seq Scan on test_idxscan (cost=0.00..15916.00 rows=100 width=61) (actual rows=100 loops=1)
Filter: ((domain_id = 0) AND (p_id = 2))
Rows Removed by Filter: 9900
Buffers: shared hit=15766
Planning Time: 0.093 ms
Execution Time: 7.483 ms
(6 rows)
postgres=# set enable_seqscan = off;
postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and p_id=2;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Index Scan using pk_test_idxscan on test_idxscan (cost=0.29..31169.27 rows=100 width=61) (actual rows=100 loops=1)
Index Cond: (domain_id = 0)
Filter: (p_id = 2)
Rows Removed by Filter: 9900
Buffers: shared hit=10055
Planning:
Buffers: shared hit=16
Planning Time: 0.135 ms
Execution Time: 6.167 ms
(9 rows)
postgres=# \dt+ test_idxscan
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+--------+-------------
public | test_idxscan | table | postgres | permanent | heap | 123 MB |
(1 row)
postgres=# \di+ pk_test_idxscan
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+-----------------+-------+----------+--------------+-------------+---------------+---------+-------------
public | pk_test_idxscan | index | postgres | test_idxscan | permanent | btree | 1144 kB |
(1 row)
При увеличении числа итераций с 300 до 650 разница заметнее: Seq Scan 14.867 ms вместо 19.750 ms, Bitmap Index Scan 3.605 ms вместо 5.830 ms. Размер таблицы 183 MB вместо 290 MB, индекс 1176 kB вместо 34 MB.
do
$$
begin
for i in 1..650 loop
with cte as (select u_id from test_idxscan limit 5000)
update test_idxscan set u_id=u_id where u_id in (select u_id from cte);
end loop;
end$$;
DO
Time: 429789.611 ms (07:09.790)
postgres=# \dt+ test_idxscan
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+--------+-------------
public | test_idxscan | table | postgres | permanent | heap | 290 MB |
(1 row)
postgres=# \di+ pk_test_idxscan
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+-----------------+-------+----------+--------------+-------------+---------------+-------+-------------
public | pk_test_idxscan | index | postgres | test_idxscan | permanent | btree | 34 MB |
(1 row)
postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and p_id=2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_idxscan (cost=17298.16..39608.30 rows=95 width=61) (actual rows=100 loops=1)
Recheck Cond: (domain_id = 0)
Filter: (p_id = 2)
Rows Removed by Filter: 9900
Heap Blocks: exact=116
Buffers: shared hit=4400
-> Bitmap Index Scan on pk_test_idxscan (cost=0.00..17298.13 rows=9830 width=0) (actual rows=10000 loops=1)
Index Cond: (domain_id = 0)
Buffers: shared hit=4284
Planning:
Buffers: shared hit=32
Planning Time: 0.215 ms
Execution Time: 5.830 ms
(13 rows)
postgres=# set enable_seqscan = on;
SET
postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and p_id=2;
QUERY PLAN
--------------------------------------------------------------------------------------------
Seq Scan on test_idxscan (cost=0.00..37193.45 rows=95 width=61) (actual rows=100 loops=1)
Filter: ((domain_id = 0) AND (p_id = 2))
Rows Removed by Filter: 9900
Buffers: shared hit=37046
Planning Time: 0.081 ms
Execution Time: 19.750 ms
(6 rows)
=================
do
$$
begin
for i in 1..650 loop
with cte as (select u_id from test_idxscan limit 5000)
update test_idxscan set u_id=u_id where u_id in (select u_id from cte);
commit;
end loop;
end$$;
DO
Time: 37388.723 ms (00:37.389)
postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and p_id=2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_idxscan (cost=663.31..19140.55 rows=100 width=61) (actual rows=100 loops=1)
Recheck Cond: (domain_id = 0)
Filter: (p_id = 2)
Rows Removed by Filter: 9900
Heap Blocks: exact=333
Buffers: shared hit=479
-> Bitmap Index Scan on pk_test_idxscan (cost=0.00..663.28 rows=10000 width=0) (actual rows=10000 loops=1)
Index Cond: (domain_id = 0)
Buffers: shared hit=146
Planning:
Buffers: shared hit=16
Planning Time: 0.158 ms
Execution Time: 3.605 ms
(13 rows)
postgres=# set enable_bitmapscan = off;
SET
postgres=# explain (analyse, timing off, buffers on) select * from test_idxscan where domain_id=0 and p_id=2;
QUERY PLAN
---------------------------------------------------------------------------------------------
Seq Scan on test_idxscan (cost=0.00..23515.00 rows=100 width=61) (actual rows=100 loops=1)
Filter: ((domain_id = 0) AND (p_id = 2))
Rows Removed by Filter: 9900
Buffers: shared hit=23365
Planning Time: 0.065 ms
Execution Time: 14.867 ms
(6 rows)
postgres=# \di+ pk_test_idxscan
List of relations
Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description
--------+-----------------+-------+----------+--------------+-------------+---------------+---------+-------------
public | pk_test_idxscan | index | postgres | test_idxscan | permanent | btree | 1176 kB |
(1 row)
postgres=# \dt+ test_idxscan
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+--------+-------------
public | test_idxscan | table | postgres | permanent | heap | 183 MB |
(1 row)Главное то, что индекс не растет, а значит время запроса при увеличении числа итераций не будет расти и останется ~4мс
Добрый день,
Спасибо за внимание к моему посту. В данном "синтетическом" случае данная ситуация с VACUUM сыграла мне на руку, мне нужно было как-то "раздуть" таблицу, чтобы воспроизвести ситуацию на ПРОМ системе. Там ситуация даже "одиознее" - соотношение pages/tuples>10, т.е. сплошные дырки, причём там одиночные UPDATE,DELETE,INSERT. Вот и хотелось поисследовать, как мы дошли до жизни такой, ладно VACUUM допустим не может ужать таблицу, т.к. пустые страницы не попадают в конец файла, но почему свободное место в страницах не переиспользуется, это вопрос.
почему свободное место в страницах не переиспользуется, это вопрос
потому, что оно не свободно, а используется старыми версиями строк, которые удерживаются: 1) долгими запросами, 2) долгими транзакциями, 3) запросами на репликах с включенной обратной связью (поэтому обратная связь по умолчанию отключена). Длительность удержания старых версий строк можно узнать в столбце secs запросом:
postgres=# select extract(epoch from (clock_timestamp()-xact_start)) secs, datname database, state from pg_stat_activity where backend_xmin IS NOT NULL OR backend_xid IS NOT NULL order by greatest(age(backend_xmin), age(backend_xid)) desc limit 10;
secs | database | state
-----------+----------+--------
94.414306 | postgres | active
0.000924 | postgres | active
(2 rows)Это пример запроса, в процессе выполнения анонимного блока DO вашего теста.
Если старые версии строк не удерживаются горизонтом базы данных или обратной связью, то автовакуум очистит место и оно будет использоваться. Пример:
После выполнения анонимного блока DO размер таблицы 134Мб, блоки полупустые:
postgres=# \dt+ test_idxscan
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+--------+-------------
public | test_idxscan | table | postgres | permanent | heap | 134 MB |
(1 row)Вставляем 99тыс. строк:
postgres=# insert into test_idxscan
select
0 as domain_id,
uuid_generate_v4() as u_id,
'somename some name name some' as name,
mod(g,100) as p_id from pg_catalog.generate_series(1,99000) g;
INSERT 0 99000
Time: 2927.706 ms (00:02.928)Все строки будут вставлены в сущестующие полупустые блоки,размер таблицы не увеличится и останется таким же, 134Мб:
postgres=# \dt+ test_idxscan
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+--------------+-------+----------+-------------+---------------+--------+-------------
public | test_idxscan | table | postgres | permanent | heap | 134 MB |
(1 row)Вы можете проверить длительность удержания горизонта на проблемной базе. Если горизонт удерживают долгие запросы, то перенести их на реплику. Обратную связь на репликах не стоит включать, для защиты от snapshot too old используют другие параметры. Если удерживают транзакции, посмотреть не простаивают ли они (столбец state в приведённом запросе).
Один «странный» случай индексного сканирования