From: Karolis Pocius <kpocius@northplains.com>
To: pgsql-performance@postgresql.org
Subject: Slow query even with aggressive auto analyze
Date: Fri, 8 Feb 2013 12:36:43 +0000
Message-ID: <5114F15B.8060700@northplains.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kpocius@northplains.com
Subject: Re: Slow query even with aggressive auto analyze
In-Reply-To: <5114F15B.8060700@northplains.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox