4 подписчика

Postgres. Visibility Map (Карта видимости)

9 июня 20239 июн 2023

10 мин

Анализ проблемы с картами видимости, влияет на работу планировщика запросов

создаем копию таблицы
create table _evnxml as select * from evnxml

переносим индексы
CREATE INDEX _evnxml_xmltemplatehtml_id_view_cond_idx ON dbo._evnxml USING btree (xmltemplatehtml_id) WHERE ((evnxml_deleted = 1) OR (evnxml_deleted IS NULL))

делаем вакум и аналайз
vacuum (full, verbose, analyze) dbo._evnxml
--> INFO: vacuuming "dbo._evnxml"
--> INFO: "_evnxml": found 0 removable, 17231681 nonremovable row versions in 2474254 pages
--DETAIL: 0 dead row versions cannot be removed yet.
--CPU: user: 78.77 s, system: 75.52 s, elapsed: 269.76 s.
--> INFO: analyzing "dbo._evnxml"
--> INFO: "_evnxml": scanned 30000 of 2474254 pages, containing 208601 live rows and 0 dead rows; 30000 rows in sample, 17204395 estimated total rows
--> OK
--> Time: 420,115s

проверяем карту видимости
select * from pg_visibility_map_summary ('_evnxml')
all_visible = 0
all_frozen = 0
или
SELECT * FROM generate_series(0,1) g(blkno), pg_visibility_map('_evnxml',g.blkno) ORDER BY g.blkno;
all_visible = false
all_frozen = false

берем реальный запрос
select
XTH.XmlTemplateHtml_HashData as "Hash",
(XT.Count + EX.Count) as "Count"
from
v_XmlTemplateHtml XTH
left join lateral (
select count(*) as Count
from v_XmlTemplate
where XmlTemplateHtml_id = XTH.XmlTemplateHtml_id
limit 1
) as XT on true
left join lateral (
select count(*) as Count
from dbo._EvnXml
where XmlTemplateHtml_id = XTH.XmlTemplateHtml_id
and ((evnxml_deleted = 1) OR (evnxml_deleted IS NULL))
limit 1
) as EX on true
where
XTH.XmlTemplateHtml_id = '50101003942685'
limit 1

время выполения 46 сек

план запроса
Limit (cost=1305.75..1313.83 rows=1 width=41) (actual time=32775.583..32775.588 rows=1 loops=1)
-> Nested Loop Left Join (cost=1305.75..1313.83 rows=1 width=41) (actual time=32775.581..32775.586 rows=1 loops=1)
-> Nested Loop Left Join (cost=12.74..20.79 rows=1 width=49) (actual time=5.596..5.599 rows=1 loops=1)
-> Index Scan using pk_xmltemplatehtml_id on xmltemplatehtml (cost=0.43..8.45 rows=1 width=41) (actual time=4.192..4.192 rows=1 loops=1)
Index Cond: (xmltemplatehtml_id = '50101003942685'::bigint)
-> Limit (cost=12.31..12.32 rows=1 width=8) (actual time=1.396..1.398 rows=1 loops=1)
-> Aggregate (cost=12.31..12.32 rows=1 width=8) (actual time=1.393..1.394 rows=1 loops=1)
-> Index Scan using _dbo_xmltemplate_xmltemplatehtml_id on xmltemplate (cost=0.42..12.30 rows=2 width=0) (actual time=1.387..1.387 rows=0 loops=1)
Index Cond: (xmltemplatehtml_id = xmltemplatehtml.xmltemplatehtml_id)
Filter: ((region_id = 5) OR (region_id IS NULL))
-> Limit (cost=1293.01..1293.02 rows=1 width=8) (actual time=32769.977..32769.978 rows=1 loops=1)
-> Aggregate (cost=1293.01..1293.02 rows=1 width=8) (actual time=32769.974..32769.975 rows=1 loops=1)
-> Index Only Scan using _evnxml_xmltemplatehtml_id_view_cond_idx on _evnxml (cost=0.44..1291.92 rows=434 width=0) (actual time=1.287..32724.742 rows=139242 loops=1)
Index Cond: (xmltemplatehtml_id = xmltemplatehtml.xmltemplatehtml_id)
Heap Fetches: 139242
Planning Time: 2.784 ms
Execution Time: 32775.693 ms

в плане присутствует использование индекса
Index Only Scan using _evnxml_xmltemplatehtml_id_view_cond_idx on _evnxml
не смотря на то что все необходимые данные присутствую непосредственно в индексе, планировщик вынужен дополднительно
обращаться к таблице для их уточнения : Heap Fetches: 139242
этот узел показывает количество обрщаний к таблице, потому что карта видимости для этого индекса не полная.
Официальные рекомендации, выполнить vacuum full, но как видно выше, карта видимости попрежнему остается не полной

делаем принудительную заморозку и аналайз
vacuum (freeze, verbose, analyze) dbo._evnxml

--> INFO: aggressively vacuuming "dbo._evnxml"
--> INFO: index "_evnxml_xmltemplatehtml_id_view_cond_idx" now contains 17122474 row versions in 46950 pages
--DETAIL: 0 index row versions were removed.
--0 index pages have been deleted, 0 are currently reusable.
--CPU: user: 0.03 s, system: 0.30 s, elapsed: 1.03 s.
--> INFO: "_evnxml": found 0 removable, 17231681 nonremovable row versions in 2474254 out of 2474254 pages
--DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 189708
--There were 0 unused item pointers.
--Skipped 0 pages due to buffer pins, 0 frozen pages.
--0 pages are entirely empty.
--CPU: user: 8.08 s, system: 28.26 s, elapsed: 247.90 s.
--> INFO: aggressively vacuuming "pg_toast.pg_toast_10964715"
--> INFO: index "pg_toast_10964715_index" now contains 5230841 row versions in 14344 pages
--DETAIL: 0 index row versions were removed.
--0 index pages have been deleted, 0 are currently reusable.
--CPU: user: 0.02 s, system: 0.09 s, elapsed: 0.63 s.
--> INFO: "pg_toast_10964715": found 0 removable, 5230841 nonremovable row versions in 1103969 out of 1103969 pages
--DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 189715
--There were 0 unused item pointers.
--Skipped 0 pages due to buffer pins, 0 frozen pages.
--0 pages are entirely empty.
--CPU: user: 7.29 s, system: 19.34 s, elapsed: 113.60 s.
--> INFO: analyzing "dbo._evnxml"
--> INFO: "_evnxml": scanned 30000 of 2474254 pages, containing 209190 live rows and 0 dead rows; 30000 rows in sample, 17252973 estimated total rows
--> OK
--> Time: 393,772s

проверяем карту видимости

select * from pg_visibility_map_summary ('_evnxml')
all_visible = 2474254
all_frozen = 2474254
или
SELECT * FROM generate_series(0,1) g(blkno), pg_visibility_map('_evnxml',g.blkno) ORDER BY g.blkno;
all_visible = true
all_frozen = true

проверяем запрос

Limit (cost=30.04..38.12 rows=1 width=41) (actual time=41.315..41.320 rows=1 loops=1)
-> Nested Loop Left Join (cost=30.04..38.12 rows=1 width=41) (actual time=41.314..41.318 rows=1 loops=1)
-> Nested Loop Left Join (cost=12.74..20.79 rows=1 width=49) (actual time=5.279..5.282 rows=1 loops=1)
-> Index Scan using pk_xmltemplatehtml_id on xmltemplatehtml (cost=0.43..8.45 rows=1 width=41) (actual time=3.254..3.254 rows=1 loops=1)
Index Cond: (xmltemplatehtml_id = '50101003942685'::bigint)
-> Limit (cost=12.31..12.32 rows=1 width=8) (actual time=2.019..2.020 rows=1 loops=1)
-> Aggregate (cost=12.31..12.32 rows=1 width=8) (actual time=2.017..2.018 rows=1 loops=1)
-> Index Scan using _dbo_xmltemplate_xmltemplatehtml_id on xmltemplate (cost=0.42..12.30 rows=2 width=0) (actual time=2.012..2.013 rows=0 loops=1)
Index Cond: (xmltemplatehtml_id = xmltemplatehtml.xmltemplatehtml_id)
Filter: ((region_id = 5) OR (region_id IS NULL))
-> Limit (cost=17.30..17.31 rows=1 width=8) (actual time=36.030..36.031 rows=1 loops=1)
-> Aggregate (cost=17.30..17.31 rows=1 width=8) (actual time=36.028..36.029 rows=1 loops=1)
-> Index Only Scan using _evnxml_xmltemplatehtml_id_view_cond_idx on _evnxml (cost=0.44..16.19 rows=443 width=0) (actual time=1.334..28.596 rows=139242 loops=1)
Index Cond: (xmltemplatehtml_id = xmltemplatehtml.xmltemplatehtml_id)
Heap Fetches: 0
Planning Time: 2.122 ms
Execution Time: 41.391 ms

Heap Fetches: 0
Execution Time: 41.391 ms

Дополнительный анализ
Повторно выполняем

vacuum (full, verbose, analyze) dbo._evnxml

--> INFO: vacuuming "dbo._evnxml"
--> INFO: "_evnxml": found 0 removable, 17231681 nonremovable row versions in 2474254 pages
--DETAIL: 0 dead row versions cannot be removed yet.
--CPU: user: 72.14 s, system: 65.68 s, elapsed: 466.71 s.
--> INFO: analyzing "dbo._evnxml"
--> INFO: "_evnxml": scanned 30000 of 2474254 pages, containing 208892 live rows and 0 dead rows; 30000 rows in sample, 17228396 estimated total rows
--> OK
--> Time: 696,452s

--проверяем карту видимости

select * from pg_visibility_map_summary ('_evnxml')
all_visible = 0
all_frozen = 0
--или
SELECT * FROM generate_series(0,1) g(blkno), pg_visibility_map('_evnxml',g.blkno) ORDER BY g.blkno;
all_visible = false
all_frozen = false

карта видимости слетела

Limit (cost=1280.67..1288.75 rows=1 width=41) (actual time=23637.334..23637.339 rows=1 loops=1)
-> Nested Loop Left Join (cost=1280.67..1288.75 rows=1 width=41) (actual time=23637.333..23637.336 rows=1 loops=1)
-> Nested Loop Left Join (cost=12.74..20.79 rows=1 width=49) (actual time=6.111..6.114 rows=1 loops=1)
-> Index Scan using pk_xmltemplatehtml_id on xmltemplatehtml (cost=0.43..8.45 rows=1 width=41) (actual time=3.298..3.299 rows=1 loops=1)
Index Cond: (xmltemplatehtml_id = '50101003942685'::bigint)
-> Limit (cost=12.31..12.32 rows=1 width=8) (actual time=2.807..2.809 rows=1 loops=1)
-> Aggregate (cost=12.31..12.32 rows=1 width=8) (actual time=2.805..2.806 rows=1 loops=1)
-> Index Scan using _dbo_xmltemplate_xmltemplatehtml_id on xmltemplate (cost=0.42..12.30 rows=2 width=0) (actual time=2.801..2.802 rows=0 loops=1)
Index Cond: (xmltemplatehtml_id = xmltemplatehtml.xmltemplatehtml_id)
Filter: ((region_id = 5) OR (region_id IS NULL))
-> Limit (cost=1267.92..1267.93 rows=1 width=8) (actual time=23631.215..23631.216 rows=1 loops=1)
-> Aggregate (cost=1267.92..1267.93 rows=1 width=8) (actual time=23631.214..23631.214 rows=1 loops=1)
-> Index Only Scan using _evnxml_xmltemplatehtml_id_view_cond_idx on _evnxml (cost=0.44..1266.85 rows=431 width=0) (actual time=0.257..23601.265 rows=139242 loops=1)
Index Cond: (xmltemplatehtml_id = xmltemplatehtml.xmltemplatehtml_id)
Heap Fetches: 139242
Planning Time: 0.767 ms
Execution Time: 23637.413 ms

делаем принудительную заморзку и аналайз
vacuum (freeze, verbose, analyze) dbo._evnxml

карта видимости полная
проверяем запрос
время выполнения = 38ms

выполняем обычный вакум
vacuum ( VERBOSE, analyze) dbo._evnxml
--> INFO: vacuuming "dbo._evnxml"
--> INFO: "_evnxml": found 0 removable, 1 nonremovable row versions in 1 out of 2474254 pages
--DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 189871
--There were 0 unused item pointers.
--Skipped 0 pages due to buffer pins, 2474253 frozen pages.
--0 pages are entirely empty.
--CPU: user: 0.04 s, system: 0.00 s, elapsed: 0.13 s.
--> INFO: vacuuming "pg_toast.pg_toast_10964715"
--> INFO: "pg_toast_10964715": found 0 removable, 4 nonremovable row versions in 1 out of 1103969 pages
--DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 189871
--There were 0 unused item pointers.
--Skipped 0 pages due to buffer pins, 1103968 frozen pages.
--0 pages are entirely empty.
--CPU: user: 0.02 s, system: 0.00 s, elapsed: 0.04 s.
--> INFO: analyzing "dbo._evnxml"
--> INFO: "_evnxml": scanned 30000 of 2474254 pages, containing 208797 live rows and 0 dead rows; 30000 rows in sample, 17220560 estimated total rows
--> OK
--> Time: 21,991s

проверяем карту видимости, она полная

время выполнения запроса 30ms

По официальной документации при выполнении VACUUM FULL, должна выполнятся заморозка, по факту этого не происходит,
в результате чего карта видимости не полная, и часть запросов выполняются по не оптимальному плану.