Re: extremly low memory usage - Mailing list pgsql-performance

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