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

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 (view raw or flat)
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

pgsql-performance by date

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

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