On Tue, Jun 16, 2020 at 1:45 PM Jim Hurne <jhu...@us.ibm.com> wrote: > Thanks Michael, > > Here are our current autovacuum settings: > > autovacuum | on > autovacuum_analyze_scale_factor | 0.1 > autovacuum_analyze_threshold | 50 > autovacuum_freeze_max_age | 200000000 > autovacuum_max_workers | 3 > autovacuum_multixact_freeze_max_age | 400000000 > autovacuum_naptime | 1min > autovacuum_vacuum_cost_delay | 20ms > autovacuum_vacuum_cost_limit | -1 > autovacuum_vacuum_scale_factor | 0.2 > autovacuum_vacuum_threshold | 50 > autovacuum_work_mem | -1 > log_autovacuum_min_duration | 1s >
With no autovacuum_vacuum_cost_limit and autovacuum_work_mem set the same as maintenance_work_mem, I wouldn't expect any difference between the performance of manual vs auto vacuum. Still, if you run a manual vacuum analyze verbose, what sort of output do you get? What is maintenance_work_mem set to? Are there indexes on this table that perhaps are very large and needing to be rescanned many times because maintenance_work_mem isn't high enough to handle in a single pass? You might try "create index concurrently, drop index concurrently, & rename index" (reindex concurrently if you were on PG 12) as a sort of online 'vacuum full' on the index(es). By the way, the best practices for these mailing list suggest partial quoting and responding in-line or below, not "top posting" with the entire conversation below.
