Re: Why is that index not used?

On Mittwoch 21 Januar 2009 Tom Lane wrote:
> > Can somebody explain me why on the last line, "dbmail_physmessage",
> > there is a seq. scan going on and no index used?
>
> Because it chose to use a hash instead.  Given the rowcounts I don't
> think that's wrong,

You mean reading 10k out of 234k entries from the table itself is
quicker than looking at the index and reading only those values needed
from the table?

> but if you want to see the other plan you can try
> setting enable_hashjoin = off (and maybe enable mergejoin = off if
> it goes to a merge join next).

Even worse:
# set enable_hashjoin = off ;
Sort (cost=12400.45..12427.22 rows=10707 width=36) (actual
time=922.751..934.921 rows=9935 loops=1)
Sort Key: msg.message_idnr
Sort Method: quicksort Memory: 1161kB
-> Merge Join (cost=3240.39..11683.82 rows=10707 width=36) (actual
time=169.998..895.009 rows=9935 loops=1)
Merge Cond: (pm.id = msg.physmessage_id)
-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage pm (cost=0.00..7681.79 rows=230034 width=24) (actual
time=0.026..407.428 rows=229873 loops=1)
-> Sort (cost=3239.57..3266.75 rows=10874 width=28) (actual
time=43.959..68.333 rows=9935 loops=1)
Sort Key: msg.physmessage_id
Sort Method: quicksort Memory: 1161kB
-> Index Scan using dbmail_messages_1 on dbmail_messages
msg (cost=0.00..2510.54 rows=10874 width=28) (actual time=2.025..23.155
rows=9935 loops=1)
Index Cond: (mailbox_idnr = 3241)
Filter: ((message_idnr >= 3196318) AND
(message_idnr <= 3619184) AND (status = ANY ('{0,1,2}'::integer[])))
Total runtime: 946.016 ms

Then in addition:
# set enable_mergejoin = off;
Sort (cost=15878.43..15905.19 rows=10707 width=36) (actual
time=343.430..358.492 rows=9935 loops=1)
Sort Key: msg.message_idnr
Sort Method: quicksort Memory: 1161kB
-> Nested Loop (cost=0.00..15161.79 rows=10707 width=36) (actual
time=2.040..314.101 rows=9935 loops=1)
-> Index Scan using dbmail_messages_1 on dbmail_messages msg
(cost=0.00..2510.54 rows=10874 width=28) (actual time=1.953..30.407
rows=9935 loops=1)
Index Cond: (mailbox_idnr = 3241)
Filter: ((message_idnr >= 3196318) AND (message_idnr <=
3619184) AND (status = ANY ('{0,1,2}'::integer[])))
-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage pm (cost=0.00..1.15 rows=1 width=24) (actual
time=0.016..0.018 rows=1 loops=9935)
Index Cond: (pm.id = msg.physmessage_id)
Total runtime: 372.209 ms

> As for your other question, the reason it likes the dbmail_messages_1
> index is probably that it's a lot smaller than dbmail_messages_7.

Sounds reasonable.

> The only thing I'm seeing that seems curious is that when forced to
> use dbmail_messages_7, it isn't using what seems to be an available
> index condition.

Yes, I simply deleted the _1 key. And I expected that _7 or _8 are used,
because they provide the same first field order, so there's no
difference in the sort order for that row. Especially since it's UNIQUE.

A general question again, because I would like to understand it:
Why can't I just delete index _1, if anyway I have
index _7 with the same field, just plus additional fields. I thought
that would be redundant:

Index _1 = mailbox_idnr
Index _7 = mailbox_idnr,status,seen_flag

So I would guess that Index _1 is redundant, and I can delete it because
the planner will use _7 instead. After all, for searching any
mailbox_idnr in that table (~234k entries) it doesn't really matter if
you use index _1 or _7, the sort is the same (if you don't care about
the other fields).

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