David,
You will no doubt hear later from the tuning experts on the list.
However, let me save everybody some time by verifying some basics:
1. When you restructured the database, you ran VACUUM ANALYZE on the new
database (pacs)?
2. You said that you "eliminated the indexes" because they weren't
helping performance. Is this right? It seems a little hard to figure
from here.
3. General Advice: If you're concerned about query performance, get rid
of those VARCHAR(80) primary keys and replace them with INT4 or INT8!
The math is easy to do:
If you're processing INT8 keys for 1,000,000 table rows that's
8,000,000 (roughly 8mb) data on disk and data being processed.
If you're processing VARCHAR(80) keys for 1,000,000 table rows, thats
82,000,000 bytes (82 mb) on disk and in ram to be processed.
In theory, you could get a 10-fold increase in JOIN performance by
switching to INT8 keys. In practice, its probably more like double or
triple but that ain't bad, either.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco