Continual Postgres headaches...

I've been trying for quite a while to get Postgresql tuned for use as an
OLTP system. I have several PL/pgSQL functions that handle inserts and
updates to the main table and several near-real-time daemons written that
access the data and can take automated actions on it (email/page concerned
people, get complimentary information from a different system, etc.). I
started with Postgres 8.1 and am now using 8.2.4 (and have been since its
release). I'll try to provide enough information for a decent response, but
as I can't obviously put my entire schema and database out there, I'm hoping
that I can get some decent guidelines beyond that what I've found though
Google, etc. to get this thing tuned better.

Most of the data centers in on a central table and has 23 columns, 1
constraint, and 9 indexes. 4 of the indexes are partial. The table usually
contains about 3-4 million rows, but I've cut it down to 1.2 million (cut
out 2/3 of the data) in an effort to migrate the database to a 2nd sever for
more testing. The two partial indexes used the most: 242MB accessed nearly
constantly, and 15MB accessed every 5 seconds - but also updated constantly
via inserts using the 242MB index. Other than one other 25MB index, the
others seem to average around 300MB each, but these aren't used quite as
often (usually about every minute or so).

My problems really are with performance consistency. I have tweaked the
execution so that everything should run with sub-second execution times, but
even after everything is running well, I can get at most a week or two of
steady running before things start to degrade.

In my efforts to correct these consistency in execution problems, I have
gone from vacuuming (with analyze) twice a day to every 30 minutes (how long
it takes a vacuum analyze to run - another seeming problem because it
shouldn't take so long?). I've done a VACUUM FULL and that sometimes helps,
but obviously is not a long-term solution for a true OLTP system. Why I
said "sometimes helps": earlier this week I did a VACUUM FULL, VACUUM
ANALYZE on the entire schema. A query that was supposed to use the 25MB
index above (that matched the parameters of the partial index exactly) was
still not doing so. I had to DROP and re-CREATE the index (and do another
ANALYZE) to get the database to (correctly) use the index.

Another problem: sometimes I get these unexplainable delays in using a
'stored procedure' even though all its SQL calls run just fine individually.
For example, calling a particular function will take 1 minute to execute
even though entering the SQL commands individually through psql will total
up to about 1 second. When I log 'long-duration queries', I only see the
top function call in the log. When I put 'RAISE NOTICE' statements in the
various PL/pgSQL functions, I only see them displayed for the function I
call directly; any underlying functions called from that function does not
show any of their NOTICE statements. Because of this I can't track down
where the delays are occurring for me to try and correct the problem (not
that there should be one in the first place)! I should also note that there
is not any lock contention (double-checked with pgadmin3) showing that would
"artificially" delay the queries...

I've played with quite a few of the parameters like vacuum_cost_delay,
work_mem, shared_buffers, turned fsync off, etc. in the postgresql.conf
file. The server I've been given to use is quite old (read: not the fastest
anymore), but normally shows a load average of 0.25.to 0.50 or so.

I guess my questions boil down to what I can do other than use the EXPLAIN,
EXPLAIN ANALYZE, VACUUM ANALYZE, etc. to get my schema performing acceptably
across the board in a consistent manner. If there is more information that
I can give out that would improve responses, please let me know.

Thanks in advance for any help you send my way!

NOTICE: This electronic mail transmission may contain confidential
information and is intended only for the person(s) named. Any use, copying
or disclosure by any other person is strictly prohibited. If you have
received this transmission in error, please notify the sender via e-mail.