|
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 |
