Re: tunning strategy needed

On 18/08/2011 6:40 AM, hyelluas wrote:
> Hello,
>
> I have an old application that was written on Postgres 8.1.
> There are a few hundreds tables, 30-40 columns per table,  hundreds of
> views,  and all the sql is inside java code.
>
> We are moving it to 8.4, it seems to be VERY slow.
> There are 20-30 tables transactions - the objects are spread acrross
> multiple tables and some tables have data from different objects.
>
> I need a short term tuning strategy minimizing rewrite&  redesign.
>
>

- Turn on auto explain and slow query logging

- Examine the slow queries and plans. Run them manually with EXPLAIN
ANALYZE. Check that the statistics make sense and if they're inaccurate,
increase the statistics targets on those columns/tables then re-ANALYZE.

- If the stats are accurate but the query is still slow, try playing
with the cost parameters and see if you get a better result, then test
those settings server-wide to see if they improve overall performance.

--
Craig Ringer