From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: AI Rumman <rummandba@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why primary key index are not using in joining?
Date: Mon, 15 Feb 2010 10:32:20 -0500
Message-ID: <29274.1266247940@sss.pgh.pa.us> (raw)
In-Reply-To: <dcc563d11002150150u2108f59eh7c5f2015cf47d367@mail.gmail.com>
References: <2a7905441002150135x1ba61b19p89f3c3b94d252843@mail.gmail.com>
<dcc563d11002150150u2108f59eh7c5f2015cf47d367@mail.gmail.com>
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Mon, Feb 15, 2010 at 2:35 AM, AI Rumman <rummandba@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, scott.marlowe@gmail.com, rummandba@gmail.com
Subject: Re: Why primary key index are not using in joining?
In-Reply-To: <29274.1266247940@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox