Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 490776327AF for ; Mon, 15 Feb 2010 11:32:34 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 21129-04 for ; Mon, 15 Feb 2010 15:32:14 +0000 (UTC) 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 883DB632796 for ; Mon, 15 Feb 2010 11:32:23 -0400 (AST) 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 o1FFWKgd029275; Mon, 15 Feb 2010 10:32:20 -0500 (EST) To: Scott Marlowe cc: AI Rumman , pgsql-performance@postgresql.org Subject: Re: Why primary key index are not using in joining? In-reply-to: References: <2a7905441002150135x1ba61b19p89f3c3b94d252843@mail.gmail.com> Comments: In-reply-to Scott Marlowe message dated "Mon, 15 Feb 2010 02:50:13 -0700" Date: Mon, 15 Feb 2010 10:32:20 -0500 Message-ID: <29274.1266247940@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.466 tagged_above=-10 required=5 tests=AWL=0.133, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/238 X-Sequence-Number: 37659 Scott Marlowe writes: > On Mon, Feb 15, 2010 at 2:35 AM, AI Rumman wrote: >> Please have a look at the following explain plan: >> Hash Join (cost=3665.17..40019.25 rows=640439 width=1603) (actual >> time=115.613..3288.436 rows=638081 loops=1) >> Hash Cond: ("outer".smownerid = "inner".id) >> -> Bitmap Heap Scan on vtiger_crmentity (cost=3646.54..30394.02 >> rows=640439 width=258) (actual time=114.763..986.504 rows=638318 loops=1) >> Recheck Cond: (deleted = 0) >> -> Bitmap Index Scan on vtiger_crmentity_deleted_idx >> (cost=0.00..3646.54 rows=640439 width=0) (actual time=107.851..107.851 >> rows=638318 loops=1) >> Index Cond: (deleted = 0) >> -> Hash (cost=18.11..18.11 rows=211 width=1345) (actual >> time=0.823..0.823 rows=211 loops=1) >> -> Seq Scan on vtiger_users (cost=0.00..18.11 rows=211 >> width=1345) (actual time=0.005..0.496 rows=211 loops=1) >> Total runtime: 3869.022 ms >> >> Sequential index is occuring on vtiger_users table while it has primary key >> index on id. > Cause it's only 211 rows and only takes 0.5 milliseconds to scan? Or, even more to the point, because a nestloop-with-inner-index-scan plan would require 638318 repetitions of the inner index scan. There's no way that is going to be a better plan than this one. Given the rowcounts --- in particular, the fact that each vtiger_users row seems to have a lot of join partners --- I don't think there *is* any better plan than this one. A nestloop with vtiger_crmentity on the inside is the only alternative worth considering, and it doesn't look like that could be any cheaper. regards, tom lane