new to postgres (and db management) and performance already a problem :-(

[Antoine <melser.anton@xxxxxxxxx>]

Hi,
We have a horribly designed postgres 8.1.0 database (not my fault!). I 
am pretty new to database design and management and have really no idea 
how to diagnose performance problems. The db has only 25-30 tables, and 
half of them are only there because our codebase needs them (long story, 
again not my fault!). Basically we have 10 tables that are being 
accessed, and only a couple of queries that join more than 3 tables. 
Most of the action takes place on two tables. One of the devs has done 
some truly atrocious coding and is using the db as his data access 
mechanism (instead of an in-memory array, and he only needs an 
array/collection).
It is running on an p4 3000ish (desktop model) running early linux 2.6 
(mdk 10.1) (512meg of ram) so that shouldn't be an issue, as we are 
talking only about 20000 inserts a day. It probably gets queried about 
20000 times a day too (all vb6 via the pg odbc).
So... seeing as I didn't really do any investigation as to setting 
default sizes for storage and the like - I am wondering whether our 
performance problems (a programme running 1.5x slower than two weeks 
ago) might not be coming from the db (or rather, my maintaining of it). 
I have turned on stats, so as to allow autovacuuming, but have no idea 
whether that could be related. Is it better to schedule a cron job to do 
it x times a day? I just left all the default values in postgres.conf... 
could I do some tweaking?
Does anyone know of any practical resources that might guide me in 
sorting out these sorts of problems? Some stuff with pratical examples 
would be good so I could compare with what we have.
Thanks
Antoine
ps. I had a look with top and it didn't look like it was going much over 
15% cpu, with memory usage negligeable. There are usually about 10 open 
connections. I couldn't find an easy way to check for disk accessings.
pps. The db is just one possible reason for our bottleneck so if you 
tell me it is very unlikely I will be most reassured!