Urgent: Tuning strategies?

Hello!

We are trying to get a new PostgreSQL-Database in production state, but
we currently experience very serious problems with performance. Data and
application (ColdFusion on four webservers, accessed via ODBC) have been
ported from an Oracle 8i database. We haven't had any experience with
PostgreSQL beforehand - that's why we're so desparate for some aid...

Here's the data I can supply:

Its a postgresql-7.2.1-installation under SuSE 7.3 on a 1GB RAM,
4xPIII550MHz Xeon machine with ~30MB storage on RAID 5 (3 disks),
filesystem is ext3.

custom-settings in postgresql.conf:
max_connections = 256
shared_buffers = 56320
wal_buffers = 32
sort_mem = 64336
wal_files = 64
fsync = false
effective_cache_size = 18200

cat /proc/sys/kernel/shmmax:
536870912

exemplary ipcs -m:
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status

0x00000000 32768 root 600 1056768 3 dest

0x00000000 98305 root 600 33554432 3 dest

0x00000000 131074 wwwrun 600 368644 3 dest

0x0052e2c1 1638403 postgres 600 472064000 33

0x07021999 229380 root 644 1104 1

A complete dump.sql of the database-installation is roughly 300MB in
size. It consists out of ten databases, one of which makes up for about
75% of all data (community-database for forums, usertables,
messaging-system), so we have a suspicion that this is where our
serverload-hog sits. This database has 33 tables, the biggest of which
as currently 237884 records and is 24 fields wide, the second biggest
has got 84512 records and is 60 fields wide.

exemplary top-output:
12:04pm up 5 days, 1:13, 3 users, load average: 2.78, 2.78, 2.89
143 processes: 138 sleeping, 5 running, 0 zombie, 0 stopped
CPU0 states: 44.0% user, 12.3% system, 0.0% nice, 43.2% idle
CPU1 states: 48.4% user, 12.1% system, 0.0% nice, 39.0% idle
CPU2 states: 48.5% user, 15.5% system, 0.0% nice, 35.1% idle
CPU3 states: 55.0% user, 12.5% system, 0.0% nice, 31.5% idle
Mem: 1029400K av, 1023660K used, 5740K free, 0K shrd, 2932K
buff
Swap: 2097136K av, 459800K used, 1637336K free 699220K
cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
29565 postgres 16 0 124M 124M 118M R 85.4 12.4 15:42 postmaster
30004 postgres 15 0 305M 305M 303M R 67.4 30.3 0:13 postmaster
29647 postgres 11 0 440M 440M 438M R 56.7 43.8 6:19 postmaster
30057 postgres 17 0 980 980 732 R 17.3 0.0 0:03 top
26122 root 10 0 1000 1000 732 R 17.0 0.0 92:15 top
29726 postgres 9 0 82536 80M 81340 S 3.1 8.0 0:56 postmaster

I vacuum-analyze regularly once a day during low-traffic times (3:00
a.m.), which takes ~3.5 minutes over all databases. Everytime we do some
big updates/inserts in the process of porting the original
Oracle-DB-data over to PostgreSQL, we do a vacuum-analyze afterwards.

The whole project feeds several websites, one of which (currently still
on Oracle) will probably cause about 20x as much load as all the rest;
we need to switch this last website over to the PostgreSQL-DB, too, and
as soon as possible. What I'd need is your opinion on the given
fundamental data (server- and OS-config, shared memory, top-output) and
some hints on how to find the bottlenecks we seem to have.

I read as much documenation as I could, but there's no "guided tour to
postgres-tuning", or at least not one that I could find. We do use
EXPLAIN-output for finding out if our indexes are used or not (and it
seems they are), but there's still so much left to guess-work, like what
bit exactly is causing the high server-loads. How can we identify these
bottlenecks? How can I find the "sweet spots" for the optimizer-settings
in postgresql.conf? What else should we or can we do in order to
maximize performance? How can we reduce swap-activity further, because I
think it's quite high (see top-output)?

I know that I haven't exactly given profound info, but unfortunately I
don't know what info you'd need in order to be able to provide specific
hints. So what info can you give me now, please, and what info can I
give you, so you could give me further assistance?

Thank you very much in advance for you help, I am (almost) sure that
with your aid we'll be able to accomplish the task :)

Regards,

Markus