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
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 |