Message-ID: <4E4CA70C.9050209@ringerc.id.au>
Date: Thu, 18 Aug 2011 13:45:48 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
To: hyelluas <helen_yelluas@mcafee.com>
CC: pgsql-performance@postgresql.org
Subject: Re: tunning strategy needed
References: <1313620811606-4710245.post@n5.nabble.com>
In-Reply-To: <1313620811606-4710245.post@n5.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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