Re: Performance Tuning

> Hello All,
>
> In contrast to what we hear from most others on this list, we find our
> database servers are mostly CPU bound. We are wondering if this is
> because
> we have postgres configured incorrectly in some way, or if we really
need
> more powerfull processor(s) to gain more performance from postgres.

Yes, many apps are not I/O bound (mine isn't). Here are factors that
are likely to make your app CPU bound:

1. Your cache hit ratio is very high
2. You have a lot of concurrency.
3. Your queries are complex, for example, doing sorting or statistics
analysis
4. Your queries are simple, but the server has to process a lot of them
(transaction overhead becomes significant) sequentially.
5. You have context switching problems, etc.

On the query side, you can tune things down considerably...try and keep
sorting down to a minimum (order on keys, avoid distinct where possible,
use 'union all', not 'union'). Basically, reduce individual query time.

Other stuff:
For complex queries, use views to cut out plan generation.
For simple but frequently run queries (select a,b,c from t where k), use
parameterized prepared statements for a 50% cpu savings, this may not be
an option in some client interfaces.

On the hardware side, you will get improvements by moving to Opteron,
etc.

Merlin