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 880095291E
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon,  8 Aug 2005 14:00:24 -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 60101-01
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon,  8 Aug 2005 17:00:12 +0000 (GMT)
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.198])
	by svr1.postgresql.org (Postfix) with ESMTP id CEDB3528FA
	for <pgsql-performance@postgresql.org>;
	Mon,  8 Aug 2005 14:00:08 -0300 (ADT)
Received: by wproxy.gmail.com with SMTP id i5so820164wra
	for <pgsql-performance@postgresql.org>;
	Mon, 08 Aug 2005 10:00:09 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=kIhXGdM880DrfcGjiVXAzVb3ra1nuikKkWzgOgV/N/i2AWfAJVH4z7Kr/egp25ybvDqvRouBhOOiL31s74OQJSi+qI4N9oA9CgL8V6Iy/Mj9QxheduQ934d2qou+EVa+vkH4ymVJ5VH/S24vniSbVtU2pHN9TfP8sXMYcTr2lXk=
Received: by 10.54.11.20 with SMTP id 20mr4996338wrk;
	Mon, 08 Aug 2005 10:00:09 -0700 (PDT)
Received: by 10.54.119.7 with HTTP; Mon, 8 Aug 2005 10:00:08 -0700 (PDT)
Message-ID: <41b0fe89050808100021a2a4fc@mail.gmail.com>
Date: Mon, 8 Aug 2005 10:00:08 -0700
From: Rhett Garber <rhettg@gmail.com>
Reply-To: Rhett Garber <rhettg@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Why hash join instead of nested loop?
Cc: =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>,
	pgsql-performance@postgresql.org
In-Reply-To: <15425.1123304631@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <41b0fe890508051135712a086b@mail.gmail.com>
	<001801c59a0a$f4be7480$9a00a8c0@OTTO>
	<41b0fe8905080516164464e04f@mail.gmail.com>
	<15425.1123304631@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.024 tagged_above=0 required=5 tests=RCVD_BY_IP
X-Spam-Level: 
X-Archive-Number: 200508/68
X-Sequence-Number: 13815

This is postgres 7.4.1

All the rows involved are integers.

Thanks,

Rhett

On 8/5/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rhett Garber <rhettg@gmail.com> writes:
> > Hash Join  (cost=3D5.96..7.04 rows=3D1 width=3D14) (actual
> > time=3D10.591..10.609 rows=3D1 loops=3D1)
> >    Hash Cond: ("outer".id =3D "inner".obj2)
> >    ->  Seq Scan on rtmessagestate  (cost=3D0.00..1.05 rows=3D5 width=3D=
14)
> > (actual time=3D0.011..0.022 rows=3D5 loops=3D1)
> >    ->  Hash  (cost=3D5.96..5.96 rows=3D1 width=3D4) (actual
> > time=3D0.109..0.109 rows=3D0 loops=3D1)
> >          ->  Index Scan using connection_regid_obj1_index on
> > connection  (cost=3D0.00..5.96 rows=3D1 width=3D4) (actual time=3D0.070=
..0.076
> > rows=3D1 loops=3D1)
> >                Index Cond: ((connection_registry_id =3D 40105) AND (obj=
1
> > =3D 73582)) Total runtime: 11.536 ms
> > (7 rows)
>=20
> [ scratches head... ]  If the hash table build takes only 0.109 msec
> and loads only one row into the hash table, and the scan of
> rtmessagestate takes only 0.022 msec and produces only 5 rows, it is
> real hard to see how the join takes 10.609 msec overall.  Unless the id
> and obj2 columns are of a datatype with an incredibly slow equality
> function.  What is the datatype involved here, anyway?  And what PG
> version are we speaking of?
>=20
>                         regards, tom lane
>