Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group