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 296D65286E for ; Fri, 5 Aug 2005 20:17:02 -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 77157-07 for ; Fri, 5 Aug 2005 23:16:51 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.195]) by svr1.postgresql.org (Postfix) with ESMTP id 78B9952A07 for ; Fri, 5 Aug 2005 20:16:50 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id i8so670635wra for ; Fri, 05 Aug 2005 16:16:51 -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=gaNGSJjfzitffKDFJqemxGJ7iBw1JZLXyiTg9M6rf7HYm+xa2Vm/Su+eQq2Uv0Psa4ceFUYNii91D94Ut4hdniuvuBX1eCYja/mZEVnh1JCyftRmp1aSto7kOYi8Hib7R7npjJz5NvQdJvzvuUG2W0QHEIXzsWh0Oct6S2AkxyQ= Received: by 10.54.27.46 with SMTP id a46mr3029134wra; Fri, 05 Aug 2005 16:16:51 -0700 (PDT) Received: by 10.54.119.7 with HTTP; Fri, 5 Aug 2005 16:16:51 -0700 (PDT) Message-ID: <41b0fe8905080516164464e04f@mail.gmail.com> Date: Fri, 5 Aug 2005 16:16:51 -0700 From: Rhett Garber Reply-To: Rhett Garber To: =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= Subject: Re: Why hash join instead of nested loop? Cc: pgsql-performance@postgresql.org In-Reply-To: <001801c59a0a$f4be7480$9a00a8c0@OTTO> 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> 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/52 X-Sequence-Number: 13799 On 8/5/05, Havasv=F6lgyi Ott=F3 wrote: > Please post the explain analyze for both queries. From that we can see th= e > predicted and the actual costs of them. > select rtmessagestate.* from rtmessagestate, connection where > connection_registry_id =3D 40105 and obj1 =3D 73582 and obj2 =3D > rtmessagestate.id; 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) > select rtmessage.id, subject from rtmessage, connection where > connection_registry_id =3D 40003 and obj1 =3D 4666 and obj2 =3D > rtmessage.id; Nested Loop (cost=3D0.00..11.62 rows=3D2 width=3D38) (actual time=3D0.186..0.970 rows=3D12 loops=3D1) -> Index Scan using connection_regid_obj1_index on connection=20 (cost=3D0.00..5.96 rows=3D1 width=3D4) (actual time=3D0.109..0.308 rows=3D1= 2 loops=3D1) Index Cond: ((connection_registry_id =3D 40003) AND (obj1 =3D 4666= )) -> Index Scan using rtmessage_pkey on rtmessage (cost=3D0.00..5.65 rows=3D1 width=3D38) (actual time=3D0.032..0.039 rows=3D1 loops=3D12) Index Cond: ("outer".obj2 =3D rtmessage.id) Total runtime: 1.183 ms (6 rows) Rhett