Re: Slow query, possibly not using index

On Mon, 28 Aug 2023 at 19:21, Les <nagylzs(at)gmail(dot)com> 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