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

Why is that index not used?

From: Michael Monnerie <michael(dot)monnerie(at)is(dot)it-management(dot)at>
To: pgsql-admin(at)postgresql(dot)org
Subject: Why is that index not used?
Date: 2009-01-21 15:34:42
Message-ID: 200901211634.42228@zmi.at (view raw or flat)
Thread:
Lists: pgsql-admin
I know I'm pedantic today but I have logs enabled to see every query 
done, and those who are called often should be as quick as possible, 
where they aren't - and I want to understand why...

Can somebody explain me why on the last line, "dbmail_physmessage", 
there is a seq. scan going on and no index used? The query says "where 
pm.id = msg.physmessage_id", and there's an index on physmessage_id, so 
what's the problem? This takes 700 of the 900ms of the query, as there 
are 230k entries in physmessage. I see that that query is parallel to 
the index scan, but that's not a good choice I think. Wouldn't it be 
better to wait for the results of the "Filter message_idnr AND status" 
and then search only the fitting physmessage_id's?

EXPLAIN ANALYZE SELECT seen_flag, answered_flag, deleted_flag, 
flagged_flag, draft_flag, recent_flag, TO_CHAR(internal_date, 'YYYY-MM-
DD HH24:MI:SS' ), rfcsize, message_idnr FROM dbmail_messages msg, 
dbmail_physmessage pm WHERE pm.id = msg.physmessage_id AND message_idnr 
BETWEEN 3196318 AND 3619184 AND mailbox_idnr = 3241 AND status IN 
(0,1,2) ORDER BY message_idnr ASC;
                                                                           QUERY 
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=10466.09..10494.28 rows=11279 width=36) (actual 
time=856.412..872.783 rows=13258 loops=1)
   Sort Key: msg.message_idnr
   Sort Method:  quicksort  Memory: 1420kB
   ->  Hash Join  (cost=6880.89..9706.93 rows=11279 width=36) (actual 
time=702.001..822.022 rows=13258 loops=1)
         Hash Cond: (msg.physmessage_id = pm.id)
         ->  Index Scan using dbmail_messages_1 on dbmail_messages msg  
(cost=0.00..2550.96 rows=11920 width=28) (actual time=0.123..30.881 
rows=13258 loops=1)
               Index Cond: (mailbox_idnr = 3241)
               Filter: ((message_idnr >= 3196318) AND (message_idnr <= 
3619184) AND (status = ANY ('{0,1,2}'::integer[])))
         ->  Hash  (cost=4004.84..4004.84 rows=230084 width=24) (actual 
time=701.458..701.458 rows=229876 loops=1)
               ->  Seq Scan on dbmail_physmessage pm  
(cost=0.00..4004.84 rows=230084 width=24) (actual time=0.015..319.395 
rows=229876 loops=1)
 Total runtime: 897.722 ms

\d dbmail_physmessage;
 id            | bigint                      | not null default 
nextval('dbmail_physmessage_id_seq'::regclass)
 messagesize   | bigint                      | not null default 
(0)::bigint
 rfcsize       | bigint                      | not null default 
(0)::bigint
 internal_date | timestamp without time zone |
Indexe:
    »dbmail_physmessage_pkey« PRIMARY KEY, btree (id) CLUSTER


mfg zmi
-- 
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4


Responses

pgsql-admin by date

Next:From: Alvaro HerreraDate: 2009-01-21 15:40:46
Subject: Re: Why is that index not used?
Previous:From: Andreas WenkDate: 2009-01-21 15:34:40
Subject: Re: autovacuum daemon

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