Re: Slow query, possibly not using index - Mailing list pgsql-performance

From David Rowley
Subject Re: Slow query, possibly not using index
Date
Msg-id CAApHDvoo9QUq4xkb9Yz_5XkMXFAkSvQZLSKNfPWAd3UuWG5PHw@mail.gmail.com
Whole thread Raw
In response to Re: Slow query, possibly not using index  (Les <nagylzs@gmail.com>)
Responses Re: Slow query, possibly not using index  (Les <nagylzs@gmail.com>)
List pgsql-performance
On Mon, 28 Aug 2023 at 19:21, Les <nagylzs@gmail.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



pgsql-performance by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: Slow query, possibly not using index
Next
From: Les
Date:
Subject: Re: Slow query, possibly not using index