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

Why primary key index are not using in joining?

From: AI Rumman <rummandba(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Why primary key index are not using in joining?
Date: 2010-02-15 09:35:01
Message-ID: 2a7905441002150135x1ba61b19p89f3c3b94d252843@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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?



 \d vtiger_users
                                                           Table
"public.vtiger_users"
       Column        |            Type
|
Modifiers
---------------------+-----------------------------+----------------------------------------------------------------------------------------------
 id                  | integer                     | not null default
nextval('vtiger_users_seq'::regclass)
 user_name           | character varying(255)      |
 user_password       | character varying(30)       |
 user_hash           | character varying(32)       |
 ...

Indexes:
    "vtiger_users_pkey" PRIMARY KEY, btree (id)
    "user_user_name_idx" btree (user_name)
    "user_user_password_idx" btree (user_password)
    "vtiger_users_user_name_lo_idx" btree (lower(user_name::text)
varchar_pattern_ops)


 \d vtiger_crmentity
                 Table "public.vtiger_crmentity"
    Column    |            Type             |     Modifiers
--------------+-----------------------------+--------------------
 crmid        | integer                     | not null
 smcreatorid  | integer                     | not null default 0
 smownerid    | integer                     | not null default 0
 modifiedby   | integer                     | not null default 0
 setype       | character varying(30)       | not null
 description  | text                        |
 createdtime  | timestamp without time zone | not null
 modifiedtime | timestamp without time zone | not null
 viewedtime   | timestamp without time zone |
 status       | character varying(50)       |
 version      | integer                     | not null default 0
 presence     | integer                     | default 1
 deleted      | integer                     | not null default 0
Indexes:
    "vtiger_crmentity_pkey" PRIMARY KEY, btree (crmid)
    "crmentity_deleted_smownerid_idx" btree (deleted, smownerid)
    "crmentity_modifiedby_idx" btree (modifiedby)
    "crmentity_smcreatorid_idx" btree (smcreatorid)
    "crmentity_smownerid_deleted_idx" btree (smownerid, deleted)
    "crmentity_smownerid_idx" btree (smownerid)
    "vtiger_crmentity_deleted_idx" btree (deleted)

Responses

pgsql-performance by date

Next:From: Dennis BjörklundDate: 2010-02-15 09:40:49
Subject: Re: 8.1 -> 8.4 regression
Previous:From: Mark MielkeDate: 2010-02-15 00:08:10
Subject: Re: Re: Faster CREATE DATABASE by delaying fsync

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