On Mon, 30 Aug 2010, Scott Marlowe wrote:
On Mon, Aug 30, 2010 at 1:25 AM, Gerhard Wiesinger <lists@xxxxxxxxxxxxx> wrote:
On Mon, 30 Aug 2010, Scott Marlowe wrote:
On Mon, Aug 30, 2010 at 12:20 AM, Gerhard Wiesinger <lists@xxxxxxxxxxxxx>
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/
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
