On Mon, 28 Aug 2023 at 19:21, Les <nagylzs@xxxxxxxxx> wrote: > More important question is, how can I find out why the index was not auto vacuumed. You should have a look at pg_stat_user_tables. It'll let you know if the table is being autovacuumed and how often. If you're concerned about autovacuum not running properly, then you might want to lower log_autovacuum_min_duration. Generally, anything that takes a conflicting lock will cause autovacuum to cancel so that the conflicting locker can get through. Things like ALTER TABLE or even an ANALYZE running will cancel most autovacuum runs on tables. Also, this is a fairly large table and you do have the standard autovacuum settings. Going by pgstattuple, the table has 39652836 tuples. Autovacuum will trigger when the statistics indicate that 20% of tuples are dead, which is about 8 million tuples. Perhaps that's enough for the index scan to have to skip over a large enough number of dead tuples to make it slow. You might want to consider lowering the autovacuum scale factor for this table. Also, ensure you're not doing anything like calling pg_stat_reset(); It might be worth showing us the output of: select * from pg_stat_user_tables where relid = 'media.block'::regclass; David
