Andrew Perrin <andrew_perrin@unc.edu> writes:
> shared_buffers is set to 128 in postgresql.conf - is that what you
> mean? There's enough RAM in this machine that I should be able to increase
> it if that will help.
Oh yes. You should be using 2000 or 3000 at least. 128 is the
absurdly low default (kept that way due to low default shared memory
settings on some systems).
> 1 Create some more indices, specifically on the id fields used to join
> tables
> 2 Increase shared_buffers as much as is practical
> 3 Try to rewrite the query without using in(select...) constructs
> 4 Move my IDE Zip drive to the secondary IDE controller to avoid IDE
> slowdowns
2, 3, and 4 will probably help. 1 may actually be
counterproductive--you need to figure out what indexes you actually
*need*. The way to do this is to VACUUM ANALYZE and then use EXPLAIN
on your queries.
> If the above aren't satisfactory:
> - Buy more RAM and repeat 2 above
> - Buy a SCSI hard drive (there's already a good SCSI controller) and move
> the database there
These certainly won't hurt, but...
> HOWEVER... what I'm hearing from most folks is that, even under these
> conditions, the performance I'm experiencing is worse than
> expected. If that's true, what should I do to diagnose that?
Use EXPLAIN. Post your schemas and the query plan that EXPLAIN gives
you, and someone here can probably make some suggestions.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863