Intermitent slow queries

From: "Parks, Aaron B(dot)" <aparks(at)rti(dot)org>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Intermitent slow queries
Date: 2007-05-02 15:24:54
Message-ID: 24F6514DFA6FD544BCA10A5C9A47E761028D86A8@rtpwexc06.RCC_NT.RTI.ORG
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

My pg 8.1 install on an AMD-64 box (4 processors) with 9 gigs of ram
running RHEL4 is acting kind of odd and I thought I would see if anybody
has any hints.

I have Java program using postgresql-8.1-409.jdbc3.jar to connect over
the network. In general it works very well. I have run batch updates
with several thousand records repeatedly that has worked fine.

The Program pulls a summation of the DB and does some processing with
it. It starts off wonderfully running a query every .5 seconds.
Unfortunately, after a while it will start running queries that take 20
to 30 seconds.

Looking at the EXPLAIN for the query no sequential scans are going on
and everything has an index that points directly at its search criteria.

Example:

Select sum(whatever) from a inner join b on a.something=b.something
WHERE b.day=1 and b.hour=1

Select sum(whatever) from a inner join b on a.something=b.something
WHERE b.day=1 and b.hour=2

Select sum(whatever) from a inner join b on a.something=b.something
WHERE b.day=1 and b.hour=3

.

.

Select sum(whatever) from a inner join b on a.something=b.something
WHERE b.day=1 and b.hour=23

Select sum(whatever) from a inner join b on a.something=b.something
WHERE b.day=1 and b.hour=24

Select sum(whatever) from a inner join b on a.something=b.something
WHERE b.day=2 and b.hour=1

Select sum(whatever) from a inner join b on a.something=b.something
WHERE b.day=2 and b.hour=2

.

.

.

This query runs fine for a while (up to thousands of times). But what
happens is that it starts to have really nasty pauses when you switch
the day condition. After the first query with the day it runs like a
charm for 24 iterations, then slows back down again

My best guess was that an index never finished running, but REINDEX on
the table (b in this case) didn't seem to help.

Ideas?

AP

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Dave Cramer 2007-05-02 18:17:56 Re: Intermitent slow queries
Previous Message david 2007-05-02 04:34:13 Re: Feature Request --- was: PostgreSQL Performance Tuning