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)
