only one index is using, why?

From: AI Rumman <rummandba(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: only one index is using, why?
Date: 2010-12-15 06:56:32
Message-ID: AANLkTimvMx3Kdfy8sU69D2V=N7UvGLCatwmYV5FKdBB7@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Marti Raudsepp 2010-12-15 08:52:03 Re: only one index is using, why?
Previous Message Mark Kirkwood 2010-12-14 22:20:38 Re: Index Bloat - how to tell?