Date: Mon, 30 Aug 2010 09:25:01 +0200 (CEST)
From: Gerhard Wiesinger <lists@wiesinger.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Major performance problem after upgrade from 8.3 to
 8.4
In-Reply-To: <AANLkTinvSHPUuZa9duQnyacZ7ySKBB07Rptb+Gt=5Xo2@mail.gmail.com>
Message-ID: <alpine.LFD.2.01.1008300912290.14250@bbs.intern>
References: <alpine.LFD.2.01.1008300802300.4121@bbs.intern>
	<AANLkTinvSHPUuZa9duQnyacZ7ySKBB07Rptb+Gt=5Xo2@mail.gmail.com>
User-Agent: Alpine 2.01 (LFD 1266 2009-07-14)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8BIT

On Mon, 30 Aug 2010, Scott Marlowe wrote:

> On Mon, Aug 30, 2010 at 12:20 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
>> Hello,
>>
>> I just upgraded with pg_dump/restore from PostgreSQL 8.3.11 to 8.4.4 but I'm
>> having major performance problems with a query with many left joins. Problem
>> is that costs are now very, very, very high (was ok in 8.3). Analyze has
>> been done. Indexes are of course there.
>>
>>  ->  Merge Left Join
>> (cost=1750660.22..4273805884876845789194861338991916289697885665127154313046252183850255795798561612107149662486528.00
>> rows=238233578115856634454073334945297075430094545596765511255148896328828230572227215727052643001958400
>> width=16)
>>        Merge Cond: (l.id = d2000903.fk_id)
>
> Wow!  Other than an incredibly high cost AND row estimate, was the
> query plan the same on 8.3 or different?
>
>> Details with execution plan can be found at:
>> http://www.wiesinger.com/tmp/pg_perf_84.txt
>
> What's up with the "(actual time=.. rows= loops=) " in the explain analyze?

What do you mean exactly? missing?
I did it not with psql but with a GUI program.

>
>> I know that the data model is key/value pairs but it worked well in 8.3. I
>> need this flexibility.
>>
>> Any ideas?
>
> Not really.  I would like an explain analyze from both 8.3 and 8.4.
> Are they tuned the same, things like work mem and default stats
> target?

I don't have a 8.3 version running anymore. But I'm havin an OLD version 
of a nearly exactly query plan (The sort was missing due to performance 
issues and it done now in a view, maybe also some more JOINS are added, 
but all that doesn't have impacts on the basic principle of the query 
plan): 
http://www.wiesinger.com/tmp/pg_perf.txt

Tuning: Yes, on same machine with same parameters (manual diff on old 
config and added manually the parameters again).

Thnx.

Ciao,
Gerhard

--
http://www.wiesinger.com/