PG's 7.2's
statistics collector may not be quite in the big leagues but it's WORLDS better than what PG had available before. Do turn it on and use it. Exceptionally long-running queries can actually be observed in realtime; otherwise, at least you can get cache & activity stats on a per-db or per-table level. If you see a lot of cache misses obviously you need to let PG grab more memory for that.
If your site is getting a lot of traffic, you'll want to play with how many threads you let nsd start simultaneously. With my site I found too many nsd threads would make PG start to thrash badly. I think this is due to the update-intensive nature of my site and my slow IDE drives. :/ It was almost magical how reducing thread count made things faster. I don't know any way to arrive at the "sweet spot" other than trial and error though. (And if you only have a couple simultaneous users it really doesn't matter. :)