On Wed, Dec 19, 2018 at 1:04 AM anand086 <anand086@xxxxxxxxx> wrote:
The Execution time for the above sql is 17841.467 ms during normal
operations but when autovacuum runs on table test_table, the same sql took
1628495.850 ms (from the postgres log).
We have noticed this increase in execution times for the sqls only when
autovacuum runs and it runs with prevent wraparound mode.
Some competition for resource is to be expected with autovacuum, but making a one-hundred fold difference in run time is rather extreme. I'd suggest that what you have is a locking issue. Something is trying to take a brief Access Exclusive lock on the table. It blocks on the lock held by the autovacuum, and then the Access Share lock needed for your query blocks behind that.
Normally an autovacuum will yield the lock when it notices it is blocking something else, but will not do so for wraparound.
If you have log_lock_waits turned on, you should see some evidence in the log file if this is the case.
Cheers,
Jeff
