Skip site navigation (1) Skip section navigation (2)

Re: Why primary key index are not using in joining?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: AI Rumman <rummandba(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Why primary key index are not using in joining?
Date: 2010-02-15 15:32:20
Message-ID: 29274.1266247940@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
> On Mon, Feb 15, 2010 at 2:35 AM, AI Rumman <rummandba(at)gmail(dot)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

In response to

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2010-02-15 15:59:27
Subject: Re: 8.1 -> 8.4 regression
Previous:From: Kevin GrittnerDate: 2010-02-15 14:33:11
Subject: Re: PostgreSQL on SMP Architectures

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group