n_dead_tup could be way off just after a vacuum
| От | Frédéric Yhuel |
|---|---|
| Тема | n_dead_tup could be way off just after a vacuum |
| Дата | |
| Msg-id | df75c0f4-cc2f-4d3e-98e3-61859aab7368@dalibo.com обсуждение исходный текст |
| Ответы |
Re: n_dead_tup could be way off just after a vacuum
|
| Список | pgsql-hackers |
My colleague Christophe Courtois and I came across some surprising behavior. After executing the following script, n_live_tup and n_dead_tup are way off. We know they are not meant to be exact, but the table will be autovacuumed for no reason afterwards. DROP TABLE IF EXISTS foo ; CREATE TABLE foo (id int); INSERT INTO foo (id) SELECT * FROM generate_series(1,1000); DELETE FROM foo WHERE id <= 500; VACUUM foo ; SELECT pg_sleep(1); SELECT relname, n_tup_ins, n_tup_del, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze, last_vacuum, last_analyze FROM pg_stat_user_tables WHERE relname='foo' \gx -[ RECORD 1 ]----+------------------------------ relname | foo n_tup_ins | 1000 n_tup_del | 500 n_live_tup | 1000 <--- should be 500 after a VACUUM n_dead_tup | 500 <--- should be 0 after a VACUUM last_autovacuum | ∅ last_autoanalyze | ∅ last_vacuum | 2026-03-10 18:11:09.893913+01 last_analyze | ∅ This issue appears on all tested versions from 9.6 to 18. If we sleep one second between the DELETE and the VACUUM, the problem disappears, because (IIUC) pgstat_relation_flush_cb() gets executed before the VACUUM. I wonder if this is a known issue, and if pg_stat_report_vacuum() should clear the pending stats, or something.
В списке pgsql-hackers по дате отправления: