Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 7A8716329C6 for ; Mon, 11 May 2009 12:06:27 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 38217-04 for ; Mon, 11 May 2009 12:06:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id BC6916328F6 for ; Mon, 11 May 2009 12:06:25 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n4BF68pU000835; Mon, 11 May 2009 11:06:08 -0400 (EDT) To: "John Lister" cc: pgsql-sql@postgresql.org Subject: Re: Query planning question In-reply-to: References: Comments: In-reply-to "John Lister" message dated "Mon, 11 May 2009 15:43:16 +0100" Date: Mon, 11 May 2009 11:06:08 -0400 Message-ID: <834.1242054368@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.14 tagged_above=0 required=5 tests=AWL=0.140 X-Spam-Level: X-Archive-Number: 200905/50 X-Sequence-Number: 32542 "John Lister" writes: > Am I right in assuming the planner thinks a sequential scan is quicker than 10k index hits, would tweaking the costs fix this or would i be better updating the stats for the product_id and manufacturer_id fields? AFAICT the planner did exactly the right things here. Your first example is fetching 40 times as many rows from retailer_offer as the second one is. If the planner had stuck with the nestloop plan, it would've taken about 40x as long, and been significantly slower than the hash join. regards, tom lane