Database tuning is a fairly complex issue and we would need to know more in order to help speed things up.
* What kind of machine are you running on (Intel P4, Mac G5, Sun Sparc, etc.)? How many CPUs? How fast are the CPUs?
* How much RAM do you have?
* How many and what kind of disks do you have? IDE? SCSI? Fibre Channel? Software RAID? Hardware RAID? Hardware RAID with cache?
* Have you separated out the WAL logs onto their own disks?
* What operating system is this running under? Windows? AIX? MacOS? What version?
* What version of Postgresql are you running?
* What filesystem type is it running on? UFS? NTFS? Ext2? Ext3? ReiserFS?
* Is the box being used for other purposes, or just dedicated to serving Pg?
* If other apps are running, how are they impacting performance?
* Is your transaction mix mostly read? Mostly write? Random or sequential reads? Random or sequential writes?
* What is the bottleneck? Is the CPU(s) pegged at 100%? Are the drives at 100%? Is all of the memory being consumed? Is the network saturated?
On the Application:
* How is the data laid out? What do the tables look like?
* How much data is in each table?
* How stable is the data? Does it change often? Rarely? Never?
* What fields of which tables are indexed how? Indexes radically slow down INSERT/UPDATE/DELETE and can (but not always) radically speed up SELECT.
* What do your queries look like? How often are each run? Once per month? Daily? 100 times per second?
* Have you analyzed the execution path of each query to compare the expected execution cost to actual execution costs? Are there ways to speed up each query?
* For queries run often (multiple times per second), are you reusing database connections or reconnecting each time? Are you reusing cached queries?
* What portions of the application are too slow?
* How much faster do you need these to be?
* Are you trying to reduce latency (response time) of small queries?
* Are you trying to increase batch throughput of long running queries?
* Are you trying to increase concurrency, i.e. reduce the slowdown as more users run the application in parallel?
Often, latency, throughput and concurrency are mutually exclusive, e.g. dropping the latency of a single fast query will reduce the batch throughput and lower concurrency.
The approach you would take to speed up Pg is dependent on the answers to these questions. Let us know the answers and we will try to help.