From: | Les <nagylzs(at)gmail(dot)com> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Slow query, possibly not using index |
Date: | 2023-08-28 11:47:22 |
Message-ID: | CAKXe9UAoWBy60jM0vkybP+iWpGDZRpUyr-vR9EWJwGUgsv8w=Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
>
>
>
> > 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
From | Date | Subject | |
---|---|---|---|
Next Message | jayaprabhakar k | 2023-08-29 00:32:38 | Index bloat and REINDEX/VACUUM optimization for partial index |
Previous Message | David Rowley | 2023-08-28 11:42:30 | Re: Slow query, possibly not using index |