Re: Slow query, possibly not using index

>
>
>
> > 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;
>
Thank you for your suggestion, this is really very helpful.

select * from pg_stat_user_tables where relid = 'media.block'::regclass;

Name |Value |
-------------------+-----------------------------+
relid |25872 |
schemaname |media |
relname |block |
seq_scan |8 |
seq_tup_read |139018370 |
idx_scan |45023556 |
idx_tup_fetch |37461539 |
n_tup_ins |7556051 |
n_tup_upd |7577720 |
n_tup_del |0 |
n_tup_hot_upd |0 |
n_live_tup |39782042 |
n_dead_tup |5938057 |
n_mod_since_analyze|1653427 |
n_ins_since_vacuum |5736676 |
last_vacuum | |
last_autovacuum |2023-08-17 22:39:29.383 +0200|
last_analyze | |
last_autoanalyze |2023-08-22 16:02:56.093 +0200|
vacuum_count |0 |
autovacuum_count |1 |
analyze_count |0 |
autoanalyze_count |4 |

Regards,

Laszlo