Performance query about large tables, lots of concurrent access

Hi,

I have an application which really exercises the performance of
postgresql in a major way, and I am running into a performance
bottleneck with Postgresql 8.1 that I do not yet understand.

Here are the details:

- There is a primary table, with some secondary tables
- The principle transaction consists of a "SELECT...FOR UPDATE",
followed by either an INSERT or an UPDATE on the primary table
- INSERTs, DELETEs, and UPDATEs may occur on the secondary table
depending on what happens with the primary table, for any given
transaction. The secondary table has about 10x the number of rows as
the primary.
- All operations are carefully chosen so that highly discriminatory
indexes are used to locate the record(s) in question. The execution
plans show INDEX SCAN operations being done in all cases.
- At any given time, there are up to 100 of these operations going on at
once against the same database.

What I am seeing:

- In postgresql 7.4, the table activity seems to be gated by locks, and
runs rather slowly except when the sizes of the tables are small.
- In postgresql 8.1, locks do not seem to be an issue, and the activity
runs about 10x faster than for postgresql 7.4.
- For EITHER database version, the scaling behavior is not the log(n)
behavior I'd expect (where n is the number of rows in the table), but
much more like linear performance. That is, as the tables grow,
performance drops off precipitously. For a primary table size up to
100,000 rows or so, I get somewhere around 700 transactions per minute,
on average. Between 100,000 and 1,000,000 rows I got some 150
transactions per minute. At about 1,500,000 rows I get about 40
transactions per minute.
- Access to a row in the secondary table (which right now has 13,000,000
rows in it) via an index that has extremely good discriminatory ability
on a busy machine takes about 90 seconds elapsed time at the moment -
which I feel is pretty high.

I tried increasing the shared_buffers parameter to see if it had any
impact on overall throughput. It was moderately helpful going from the
small default value up to 8192, but less helpful when I increased it
beyond that. Currently I have it set to 131072.

Question: Does anyone have any idea what bottleneck I am hitting? An
index's performance should in theory scale as the log of the number of
rows - what am I missing here?

Thanks very much!
Karl