Re: 7.4 dramatically slower than 7.3? (was: snowflaking)

From: Nabil Sayegh <postgresql(at)e-trolley(dot)de>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)org>, Joe Conway <mail(at)joeconway(dot)com>
Subject: Re: 7.4 dramatically slower than 7.3? (was: snowflaking)
Date: 2004-04-06 13:22:56
Message-ID: 4072AF30.4070008@e-trolley.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

When I take the query of my first post and EXPLAIN ANALYZE it with 7.3 I get the following output:
----------------------------------------------------------------------------------------------------
Hash Join (cost=85.66..213.16 rows=1 width=240) (actual time=34.01..34.47 rows=1 loops=1)
Hash Cond: ("outer".id_objekt = "inner".id_objekt)
-> Hash Join (cost=80.98..208.48 rows=1 width=228) (actual time=10.49..10.94 rows=1 loops=1)
Hash Cond: ("outer".id_objekt = "inner".id_objekt)
-> Nested Loop (cost=69.75..197.24 rows=1 width=216) (actual time=9.65..10.09 rows=1
loops=1)
Join Filter: ("inner".id_objekt = "outer".id2_objekt)
-> Nested Loop (cost=67.83..185.99 rows=1 width=204) (actual time=9.17..9.58
rows=1 loops=1)
Join Filter: ("inner".id_objekt = "outer".id2_objekt)
-> Nested Loop (cost=65.91..174.72 rows=1 width=192) (actual time=8.68..9.08
rows=1 loops=1)
Join Filter: ("inner".id_objekt = "outer".id2_objekt)
-> Nested Loop (cost=63.99..163.44 rows=1 width=180) (actual
time=8.19..8.58 rows=1 loops=1)
Join Filter: ("inner".id_objekt = "outer".id2_objekt)
-> Hash Join (cost=59.66..149.14 rows=1 width=164) (actual
time=7.63..8.00 rows=1 loops=1)
Hash Cond: ("outer".id_objekt = "inner".id_objekt)
-> Nested Loop (cost=45.61..135.07 rows=1 width=148)
(actual time=6.48..6.83 rows=1
[etc etc etc]
----------------------------------------------------------------------------------------------------

Now I tested it with 7.4 to see if gets faster, but guess what?
It's about 40 times slower(!):

----------------------------------------------------------------------------------------------------
Merge Left Join (cost=142.22..142.28 rows=2 width=0) (actual time=18.681..18.684 rows=1 loops=1)
Merge Cond: ("outer".id_objekt = "inner".id_objekt)
-> Sort (cost=136.19..136.20 rows=2 width=4) (actual time=18.464..18.465 rows=1 loops=1)
Sort Key: o.id_objekt
-> Hash Left Join (cost=136.01..136.18 rows=2 width=4) (actual time=18.289..18.293
rows=1 loops=1)
Hash Cond: ("outer".id_objekt = "inner".id_objekt)
-> Merge Left Join (cost=129.95..130.03 rows=2 width=4) (actual
time=17.860..17.863 rows=1 loops=1)
Merge Cond: ("outer".id2_objekt = "inner".id_objekt)
-> Sort (cost=123.05..123.06 rows=2 width=8) (actual time=17.568..17.569
rows=1 loops=1)
Sort Key: public.objekt_objekt.id2_objekt
-> Hash Left Join (cost=122.77..123.04 rows=2 width=8) (actual
time=17.543..17.547 rows=1 loops=1)
Hash Cond: ("outer".id2_objekt = "inner".id_objekt)
-> Merge Left Join (cost=116.05..116.13 rows=2 width=12) (actual
time=16.933..16.936 rows=1 loops=1)
Merge Cond: ("outer".id2_objekt = "inner".id_objekt)
-> Sort (cost=109.11..109.12 rows=2 width=12) (actual
time=16.622..16.623 rows=1 loops=1)
[etc etc etc]
----------------------------------------------------------------------------------------------------

Any idea?

Additional Information:
I'm JOINing exclusively on PKeys/FKeys and the WHERE clause also only uses PKeys (these should have
indexes automatically, right?)
I did VACUUM ANALYZE on both machines, didn't help.

--
e-Trolley Sayegh & John, Nabil Sayegh
Tel.: 0700 etrolley /// 0700 38765539
Fax.: +49 69 8299381-8
PGP : http://www.e-trolley.de

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Stephan Szabo 2004-04-06 14:20:04 Re: 7.4 dramatically slower than 7.3? (was: snowflaking)
Previous Message Tom Lane 2004-04-06 04:31:51 Re: multiple statements.. and locking