I have a table in Postgresql 9.0.1 as folllows:
Table "public.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:
"crmentity_pkey" PRIMARY KEY, btree (crmid)
"crmentity_createdtime_idx" btree (createdtime)
"crmentity_modifiedby_idx" btree (modifiedby)
"crmentity_modifiedtime_idx" btree (modifiedtime)
"crmentity_smcreatorid_idx" btree (smcreatorid)
"crmentity_smownerid_idx" btree (smownerid)
"ftx_crmentity_descr" gin (to_tsvector('english'::regconfig, replace(description, '<!--'::text, '<!-'::text)))
"crmentity_deleted_idx" btree (deleted)
"crmentity_setype_idx" btree (setype)
Referenced by:
TABLE "service" CONSTRAINT "fk_1_service" FOREIGN KEY (serviceid) REFERENCES crmentity(crmid) ON DELETE CASCADE
TABLE "_cc2crmentity" CONSTRAINT "fk__cc2crmentity_crmentity" FOREIGN KEY (crm_id) REFERENCES crmentity(crmid) ON UPDATE CASCADE ON DELETE CASCADE
EXPLAIN ANALYZE on this table:
explain analyze
select *
FROM crmentity
where crmentity.deleted=0 and crmentity.setype='Emails'
Index Scan using crmentity_setype_idx on crmentity (cost=0.00..1882.76 rows=55469 width=301) (actual time=0.058..158.564 rows=79193 loops=1)
Index Cond: ((setype)::text = 'Emails'::text)
Filter: (deleted = 0)
Total runtime: 231.256 ms
(4 rows)
My question is why "crmentity_setype_idx" index is being used only. "crmentity_deleted_idx" index is not using.
Any idea please.