X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 38DAB9DCDC6 for ; Tue, 21 Feb 2006 01:36:02 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93746-09 for ; Tue, 21 Feb 2006 01:36:01 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id EA8569DCDC4 for ; Tue, 21 Feb 2006 01:35:57 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k1L5Zt6H004361; Tue, 21 Feb 2006 00:35:56 -0500 (EST) To: "Virag Saksena" cc: pgsql-performance@postgresql.org Subject: Re: Cost Issue - How do I force a Hash Join In-reply-to: <028401c636a5$907fb4b0$3100000a@demo1> References: <028401c636a5$907fb4b0$3100000a@demo1> Comments: In-reply-to "Virag Saksena" message dated "Mon, 20 Feb 2006 21:13:34 -0800" Date: Tue, 21 Feb 2006 00:35:55 -0500 Message-ID: <4360.1140500155@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.11 required=5 tests=[AWL=0.110] X-Spam-Score: 0.11 X-Spam-Level: X-Archive-Number: 200602/324 X-Sequence-Number: 17310 "Virag Saksena" writes: > The individual queries run in 50-300 ms. However the optimizer is = > choosing a nested loop to join them rather than a Hash join > causing the complete query to take 500+ seconds. It expects that it will = > get 1 row out from each of the sources, but here is gets > several thousand rows. The best approach is to see if you can't fix that estimation error. Are the stats up to date on these tables? If so, maybe raising the statistics targets would help. regards, tom lane