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

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

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: AI Rumman <rummandba(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why primary key index are not using in joining?
Date: 2010-02-15 09:50:13
Message-ID: dcc563d11002150150u2108f59eh7c5f2015cf47d367@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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:
>
> explain analyze
> select *
> from vtiger_crmentity
> inner JOIN vtiger_users
>          ON vtiger_users.id = vtiger_crmentity.smownerid
> where vtiger_crmentity.deleted = 0  ;
>
> QUERY
> 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.
> Could anyone please tell me why?

Cause it's only 211 rows and only takes 0.5 milliseconds to scan?

In response to

Responses

pgsql-performance by date

Next:From: lionel duboeufDate: 2010-02-15 09:52:49
Subject: Re: Almost infinite query -> Different Query Plan when changing where clause value
Previous:From: Dennis BjörklundDate: 2010-02-15 09:40:49
Subject: Re: 8.1 -> 8.4 regression

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