X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 99F9F528DB
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue,  9 Aug 2005 02:12:10 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 47510-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue,  9 Aug 2005 05:12:01 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id 4249B5287F
	for <pgsql-performance@postgresql.org>;
	Tue,  9 Aug 2005 02:12:00 -0300 (ADT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j795Bnnh044438
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Mon, 8 Aug 2005 23:11:52 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j795Bn6k085523;
	Mon, 8 Aug 2005 23:11:49 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j795Bmmj085522;
	Mon, 8 Aug 2005 23:11:48 -0600 (MDT) (envelope-from mfuhr)
Date: Mon, 8 Aug 2005 23:11:48 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Rhett Garber <rhettg@gmail.com>,
	=?iso-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>,
	pgsql-performance@postgresql.org
Subject: Re: Why hash join instead of nested loop?
Message-ID: <20050809051148.GA85494@winnie.fuhr.org>
References: <41b0fe890508051135712a086b@mail.gmail.com>
	<001801c59a0a$f4be7480$9a00a8c0@OTTO>
	<41b0fe8905080516164464e04f@mail.gmail.com>
	<15425.1123304631@sss.pgh.pa.us>
	<41b0fe89050808100021a2a4fc@mail.gmail.com>
	<10930.1123549106@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <10930.1123549106@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200508/75
X-Sequence-Number: 13822

On Mon, Aug 08, 2005 at 08:58:26PM -0400, Tom Lane wrote:
> I'd be interested to see results from other people using 7.4.* too.

I just built 7.4.1 on FreeBSD 4.11-STABLE and ran your test:

test=# 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.220..0.264 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.015..0.050 rows=5 loops=1)
   ->  Hash  (cost=4.83..4.83 rows=1 width=4) (actual time=0.103..0.103 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.070..0.081 rows=1 loops=1)
               Index Cond: ((connection_registry_id = 40105) AND (obj1 = 73582))
 Total runtime: 0.495 ms
(7 rows)

-- 
Michael Fuhr