Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 6F57A2E2D7D for ; Fri, 16 Nov 2007 11:56:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 15522-08 for ; Fri, 16 Nov 2007 11:56:25 -0400 (AST) Received: from globalrelay.com (mail1.globalrelay.com [204.92.49.200]) by postgresql.org (Postfix) with ESMTP id 7DF1A2E2D82 for ; Fri, 16 Nov 2007 11:56:31 -0400 (AST) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway Received: from [63.231.179.94] (HELO davedell) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 164760335; Fri, 16 Nov 2007 15:56:29 +0000 From: "Dave Dutcher" To: "'Ow Mun Heng'" Cc: References: <47344D43.5090506@geri.cc.fer.hr> <47347F9F.1070309@laliluna.de> <1195206185.29253.56.camel@neuromancer.home.net> Subject: Re: PostgreSQL vs MySQL, and FreeBSD Date: Fri, 16 Nov 2007 09:56:28 -0600 Message-ID: <01c601c82869$3ffb2580$8e00a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcgoNVtJjK7DuZ+0QTq/zVUIVAGPngAMmhPw X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 In-Reply-To: <1195206185.29253.56.camel@neuromancer.home.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/242 X-Sequence-Number: 27657 > -----Original Message----- > From: Ow Mun Heng > Subject: Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD > > Even for Postgresql, nested loops are still evil and hampers > performance. I don't know about that. There are times when it is the right plan: explain analyze select * from table1 t1 inner join table2 t2 on t1.f_id = t2.id where t1.id = 'xyzzy'; QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------- Nested Loop (cost=0.00..17.65 rows=1 width=344) (actual time=0.080..0.096 rows=1 loops=1) -> Index Scan using table1_pkey on table1 t (cost=0.00..9.18 rows=1 width=238) (actual time=0.044..0.048 rows=1 loops=1) Index Cond: ((id)::text = 'xyzzy'::text) -> Index Scan using table2_pkey on table2 i (cost=0.00..8.46 rows=1 width=106) (actual time=0.019..0.023 rows=1 loops=1) Index Cond: (t.f_id = i.id) Total runtime: 0.224 ms set enable_nestloop=off; SET explain analyze select * from table1 t1 inner join table2 t2 on t1.f_id = t2.id where t1.id = 'xyzzy'; QUERY PLAN ---------------------------------------------------------------------------- ------------------------------------------------------------ Hash Join (cost=9.18..72250.79 rows=1 width=344) (actual time=13493.572..15583.049 rows=1 loops=1) Hash Cond: (i.id = t.f_id) -> Seq Scan on table2 i (cost=0.00..61297.40 rows=2188840 width=106) (actual time=0.015..8278.347 rows=2188840 loops=1) -> Hash (cost=9.18..9.18 rows=1 width=238) (actual time=0.056..0.056 rows=1 loops=1) -> Index Scan using table1_pkey on table1 t (cost=0.00..9.18 rows=1 width=238) (actual time=0.040..0.045 rows=1 loops=1) Index Cond: ((id)::text = 'xyzzy'::text) Total runtime: 15583.212 ms (I changed the table names, but everything else is real.)