Re: Why hash join instead of nested loop?

From: Steinar H. Gunderson
Subject: Re: Why hash join instead of nested loop?
Date: ,
Msg-id: 20050809010850.GA18938@uio.no
(view: Whole thread, Raw)
In response to: Re: Why hash join instead of nested loop?  (Tom Lane)
List: pgsql-performance

Tree view

Why hash join instead of nested loop?  (Rhett Garber, )
 Re: Why hash join instead of nested loop?  (Havasvölgyi Ottó, )
  Re: Why hash join instead of nested loop?  (Rhett Garber, )
   Re: Why hash join instead of nested loop?  (Tom Lane, )
    Re: Why hash join instead of nested loop?  (Rhett Garber, )
     Re: Why hash join instead of nested loop?  (Tom Lane, )
      Re: Why hash join instead of nested loop?  ("Steinar H. Gunderson", )
      Re: Why hash join instead of nested loop?  (Michael Fuhr, )
      Re: Why hash join instead of nested loop?  (Ian Westmacott, )
       Re: Why hash join instead of nested loop?  (Tom Lane, )
        Re: Why hash join instead of nested loop?  (Ian Westmacott, )
      Re: Why hash join instead of nested loop?  (Rhett Garber, )
       Re: Why hash join instead of nested loop?  (Tom Lane, )
        Re: Why hash join instead of nested loop?  (Rhett Garber, )
         Re: Why hash join instead of nested loop?  (Tom Lane, )
          Re: Why hash join instead of nested loop?  (Rhett Garber, )
           Re: Why hash join instead of nested loop?  (Tom Lane, )
            Re: Why hash join instead of nested loop?  (Rhett Garber, )

On Mon, Aug 08, 2005 at 08:58:26PM -0400, Tom Lane wrote:
> Hmph.  There is something really strange going on here.  I tried to
> duplicate your problem in 7.4.*, thus:

PostgreSQL 7.4.7 (Debian sarge):

<create table and stuff, exactly the same as you>

regression=# explain analyze select rtmessagestate.* from rtmessagestate,connection where (connection_registry_id =
40105)AND (obj1  = 73582) and id = obj2; 

                                                                     QUERY PLAN
                            

----------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=4.83..5.91 rows=1 width=14) (actual time=0.155..0.159 rows=1 loops=1)
   Hash Cond: ("outer".id = "inner".obj2)
   ->  Seq Scan on rtmessagestate  (cost=0.00..1.05 rows=5 width=14) (actual time=0.003..0.006 rows=5 loops=1)
   ->  Hash  (cost=4.83..4.83 rows=1 width=4) (actual time=0.026..0.026 rows=0 loops=1)
         ->  Index Scan using connection_regid_obj1_index on connection  (cost=0.00..4.83 rows=1 width=4) (actual
time=0.011..0.012rows=1 loops=1) 
               Index Cond: ((connection_registry_id = 40105) AND (obj1 = 73582))
 Total runtime: 0.215 ms
(7 rows)

This is an Opteron (in 32-bit mode), though.

/* Steinar */
--
Homepage: http://www.sesse.net/


pgsql-performance by date:

From: Michael Fuhr
Date:
Subject: Re: Why hash join instead of nested loop?
From: Ian Westmacott
Date:
Subject: Re: Why hash join instead of nested loop?