Обсуждение: only one index is using, why?

Поиск
Список
Период
Сортировка

only one index is using, why?

От
AI Rumman
Дата:
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.

Re: only one index is using, why?

От
Marti Raudsepp
Дата:
On Wed, Dec 15, 2010 at 08:56, AI Rumman <rummandba@gmail.com> wrote:
> My question is why "crmentity_setype_idx" index is being used only.
> "crmentity_deleted_idx" index is not using.
> Any idea please.

Because the planner determined that the cost of scanning *two* indexes
and combining the results is more expensive than scanning one index
and filtering the results afterwards.

Looks like your query could use a composite index on both columns:
(deleted, setype)
Or a partial index:  (setype) WHERE deleted=0

Regards,
Marti