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.
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? |