Slow query even with aggressive auto analyze

From: Karolis Pocius <kpocius(at)northplains(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Slow query even with aggressive auto analyze
Date: 2013-02-08 12:36:43
Message-ID: 5114F15B.8060700@northplains.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Stefan Keller 2013-02-08 17:30:06 Re: FTS performance issue probably due to wrong planner estimate of detoasting
Previous Message Jesper Krogh 2013-02-08 06:56:03 Re: FTS performance issue probably due to wrong planner estimate of detoasting