Thread: Slow query even with aggressive auto analyze
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?
On Friday, February 08, 2013 6:06 PM Karolis Pocius wrote: > 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? I think you can verify in Logs whether analyze is happening as per your expectation. You can set log_autovacuum_min_duration = 0, so that auto_analyze can be logged everytime it happens. With Regards, Amit Kapila.