Re: Major performance problem after upgrade from 8.3 to 8.4

On Mon, 30 Aug 2010, Scott Marlowe wrote:

> On Mon, Aug 30, 2010 at 1:25 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
>> 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.
>
> Nevermind, that was an artifact at http://explain.depesz.com/s/KyU not
> your fault.  Sorry.
>
>>>> 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).
>
> How long does the query take to run in 8.4?  Do you have an explain
> analyze of that?  I'm still thinking that some change in the query
> planner might be seeing all those left joins and coming up with some
> non-linear value for row estimation.  What's default stats target set
> to in that db?

In config, default values:
#default_statistics_target = 100        # range 1-10000

How can I find that out?

Ciao,
Gerhard

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