Thank you... so to make it explicit, this table has 15169372 rows, autovacuum_analyze_scale_factor is .05, and autovacuum_analyze_threshold is 10, so: 15169372 * .05 + 10, which is 758478.60 -- so that means auto-analyze will only fire when that number of rows has been deleted or updated?
On Wed, Apr 21, 2021 at 1:48 PM Keith Fiske <keith.fiske@xxxxxxxxxxxxxxx> wrote:
On Wed, Apr 21, 2021 at 4:06 PM Wells Oliver <wells.oliver@xxxxxxxxx> wrote:A last_autoanalyze date in pg_stat_all_tables for a table we update every day is 3/26, another is 3/13 -- I feel strongly somehow these tables should be auto-analyzed and auto-vacuumed more often, and am wondering where to look first.My autovacuum_analyze_threshold is 10, my autovacuum_vacuum_threshold is 25.How can I better understand why these tables are not more frequently being auto-analyzed and vacuumed?Thank you.The threshold settings are only part of the calculation that determines when autovacuum kicks in. There's also autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor. The scale factors say that when this fraction of the table has been changed (updates/deletes) then run the relevant operation (vacuum or analyze).The threshold values are added on top of the scale factor values. So, what determines whether your analyze kicks in is actually(autovacuum_analyze_scale_factor * total row count) + autovacuum_analyze_thresholdThe reason it's like this is to avoid autovacuum kicking in excessively on very small tables when a small % changes. Ex. 10 rows on a 100 row table constantly kicking in autovacuum when scale factor is set to .10. So add on 100-500 for the threshold so autovacuum kicks in when a good number of rows actually changes.If you want a specific number of row changes to cause autovacuum to kick in, you can set the scale factor to zero for that specific table. I only really find that necessary myself on rather large tables though (10s of millions of rows).--
Wells Oliver
wells.oliver@xxxxxxxxx
wells.oliver@xxxxxxxxx
