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=LzdUZh81WS8v66j92VyOpvduWrFrPEQRslzeS0eBEktjBUyupTqqXFrEemd0HD0chD7pNJPWIgSmaQOGiyHLSsC2gf4zP1PJIe9nDCG6CAuEBTDKjh3PwVfU4hCjjX0vxbu8gHUkQFfWnWxwjCxEG0nqJ1A5smamKzp06EAqYqs=
Message-ID: <41b0fe890508090951546156f7@mail.gmail.com>
Date: Tue, 9 Aug 2005 09:51:51 -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: <10930.1123549106@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>
	<41b0fe89050808100021a2a4fc@mail.gmail.com>
	<10930.1123549106@sss.pgh.pa.us>

Duplicated your setup in a separate DB.

At least its reproducable for me.....

I tested this on a Xeon 2 Ghz, 1 Gig Ram. Its running on some shared
storage array that I'm not sure the details of.

My production example also shows up on our production machine that is
almost the same hardware but has dual zeon and 6 gigs of ram.

Rhett

Hash Join  (cost=3D4.83..5.91 rows=3D1 width=3D14) (actual time=3D7.148..7.=
159
rows=3D1 loops=3D1)
   Hash Cond: ("outer".id =3D "inner".obj2)
   ->  Seq Scan on rtmessagestate  (cost=3D0.00..1.05 rows=3D5 width=3D14)
(actual time=3D0.007..0.015 rows=3D5 loops=3D1)
   ->  Hash  (cost=3D4.83..4.83 rows=3D1 width=3D4) (actual
time=3D0.055..0.055 rows=3D0 loops=3D1)
         ->  Index Scan using connection_regid_obj1_index on
connection  (cost=3D0.00..4.83 rows=3D1 width=3D4) (actual time=3D0.028..0.=
032
rows=3D1 loops=3D1)
               Index Cond: ((connection_registry_id =3D 40105) AND (obj1
=3D 73582)) Total runtime: 7.693 ms
(7 rows)


On 8/8/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Rhett Garber <rhettg@gmail.com> writes:
> > This is postgres 7.4.1
> > All the rows involved are integers.
>=20
> Hmph.  There is something really strange going on here.  I tried to
> duplicate your problem in 7.4.*, thus:
>=20
> regression=3D# create table rtmessagestate(id int, f1 char(6));
> CREATE TABLE
> regression=3D# insert into rtmessagestate values(1,'z');
> INSERT 559399 1
> regression=3D# insert into rtmessagestate values(2,'z');
> INSERT 559400 1
> regression=3D# insert into rtmessagestate values(3,'z');
> INSERT 559401 1
> regression=3D# insert into rtmessagestate values(4,'z');
> INSERT 559402 1
> regression=3D# insert into rtmessagestate values(5,'z');
> INSERT 559403 1
> regression=3D# vacuum analyze rtmessagestate;
> VACUUM
> regression=3D# create table connection(connection_registry_id int, obj1 i=
nt, obj2 int);
> CREATE TABLE
> regression=3D# create index connection_regid_obj1_index on connection(con=
nection_registry_id,obj1);
> CREATE INDEX
> regression=3D# insert into  connection values(40105,73582,3);
> INSERT 559407 1
> regression=3D# explain analyze select rtmessagestate.* from rtmessagestat=
e,connection where (connection_registry_id =3D  40105) AND (obj1  =3D 73582=
) and id =3D obj2;
>                                                                      QUER=
Y PLAN
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------
>  Hash Join  (cost=3D4.83..5.91 rows=3D1 width=3D14) (actual time=3D0.498.=
.0.544 rows=3D1 loops=3D1)
>    Hash Cond: ("outer".id =3D "inner".obj2)
>    ->  Seq Scan on rtmessagestate  (cost=3D0.00..1.05 rows=3D5 width=3D14=
) (actual time=3D0.030..0.072 rows=3D5 loops=3D1)
>    ->  Hash  (cost=3D4.83..4.83 rows=3D1 width=3D4) (actual time=3D0.305.=
.0.305 rows=3D0 loops=3D1)
>          ->  Index Scan using connection_regid_obj1_index on connection  =
(cost=3D0.00..4.83 rows=3D1 width=3D4) (actual time=3D0.236..0.264 rows=3D1=
 loops=3D1)
>                Index Cond: ((connection_registry_id =3D 40105) AND (obj1 =
=3D 73582))
>  Total runtime: 1.119 ms
> (7 rows)
>=20
> This duplicates your example as to plan and row counts:
>=20
> > 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=3D14)
> > (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 (obj1
> > =3D 73582)) Total runtime: 11.536 ms
> > (7 rows)
>=20
> My machine is considerably slower than yours, to judge by the actual
> elapsed times in the scan nodes ... so why is it beating the pants
> off yours in the join step?
>=20
> Can you try the above script verbatim in a scratch database and see
> what you get?  (Note it's worth trying the explain two or three
> times to be sure the values have settled out.)
>=20
> I'm testing a fairly recent 7.4-branch build (7.4.8 plus), so that's one
> possible reason for the discrepancy between my results and yours, but I
> do not see anything in the 7.4 CVS logs that looks like it's related to
> hashjoin performance.
>=20
> I'd be interested to see results from other people using 7.4.* too.
>=20
>                         regards, tom lane
>