At 01:18 PM 8/19/2005, John A Meinel wrote:
Jeremiah Jahn wrote:
> Sorry about the formatting.
>
> On Thu, 2005-08-18 at 12:55 -0500, John Arbash Meinel wrote:
>
>>Jeremiah Jahn wrote:
>>
>>
...
>>The expensive parts are the 4915 lookups into the litigant_details (each
>>one takes approx 4ms for a total of ~20s).
>>And then you do it again on case_data (average 3ms each * 4906 loops =
>>~15s).
>
> Is there some way to avoid this?
>
Well, in general, 3ms for a single lookup seems really long. Maybe your
index is bloated by not vacuuming often enough. Do you tend to get a lot
of updates to litigant_details?
Given that the average access time for a 15Krpm HD is in the 5.5-6ms
range (7.5-8ms for a 10Krpm HD), having an average of 3ms for a
single lookup implies that ~1/2 (the 15Krpm case) or ~1/3 (the 10Krpm
case) table accesses is requiring a seek.
This implies a poor match between physical layout and access pattern.
If I understand correctly, the table should not be very fragmented
given that this is a reasonably freshly loaded DB? That implies that
the fields being looked up are not well sorted in the table compared
to the query pattern.
If the entire table could fit in RAM, this would be far less of a
consideration. Failing that, the physical HD layout has to be
improved or the query pattern has to be changed to reduce seeks.
There are a couple possibilities at this point. First, you can REINDEX
the appropriate index, and see if that helps. However, if this is a test
box, it sounds like you just did a dump and reload, which wouldn't have
bloat in an index.
Another possibility. Is this the column that you usually use when
pulling information out of litigant_details? If so, you can CLUSTER
litigant_details on the appropriate index. This will help things be
close together that should be, which decreases the index lookup costs.
However, if this is not the common column, then you probably will slow
down whatever other accesses you may have on this table.
After CLUSTER, the current data will stay clustered, but new data will
not, so you have to continually CLUSTER, the same way that you might
VACUUM. *However*, IIRC CLUSTER grabs an Exclusive lock, so it is as
expensive as a VACUUM FULL. Be aware of this, but it might vastly
improve your performance, so it would be worth it.
CLUSTER can be a very large maintenance overhead/problem if the
table(s) in question actually need to be "continually" re CLUSTER ed.
If there is no better solution available, then you do what you have
to, but it feels like there should be a better answer here.
Perhaps the DB schema needs examining to see if it matches up well
with its real usage?
Ron Peacetree
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
