Slow query even with aggressive auto analyze
От | Karolis Pocius |
---|---|
Тема | Slow query even with aggressive auto analyze |
Дата | |
Msg-id | 5114F15B.8060700@northplains.com обсуждение исходный текст |
Ответы |
Re: Slow query even with aggressive auto analyze
|
Список | pgsql-performance |
This query http://pgsql.privatepaste.com/359bed8e9e gets executed every 500 ms and normally completes really quickly http://explain.depesz.com/s/poVQ, but the more job_batches table (http://pgsql.privatepaste.com/eaf6d63fd2) gets used, the slower this query gets, to the point where it takes minutes to execute http://explain.depesz.com/s/rDJ. Analyzing job_batches table resolves the issue immediately. This table gets about a thousand new records an hour that are also updated three times as the status changes. No deletion is occurring. I've tried changing autovacuum_analyze_scale_factor as well as setting job_batches table to auto analyze every 500 changes (by setting scale factor to 0 and threshold to 500), but I still keep running into that issue, sometimes minutes after the table was analyzed. I checked pg_locks to see if anything had granted=false, but that doesn't seem to be the case. This issue is occurring on two separate instances 9.0.4 and 9.1.4 - both have nearly identical settings, just run on a different hardware. Config changes http://pgsql.privatepaste.com/8acfb9d136 Any ideas what is going wrong here?
В списке pgsql-performance по дате отправления: