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?
--
To understand recursion, one must first understand recursion.