Message-ID: <5114F15B.8060700@northplains.com>
Date: Fri, 8 Feb 2013 12:36:43 +0000
From: Karolis Pocius <kpocius@northplains.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130106 Thunderbird/17.0.2
MIME-Version: 1.0
To: <pgsql-performance@postgresql.org>
Subject: Slow query even with aggressive auto analyze
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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?


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance