Re: Question on Index usage

Michael Monnerie wrote:

> EXPLAIN ANALYZE SELECT 1 FROM dbmail_messages msg JOIN
> dbmail_physmessage pm ON ( pm.id = msg.physmessage_id ) WHERE
> message_idnr BETWEEN 3178782 AND 3616157 AND mailbox_idnr = 3236 AND
> status IN (0,1,2) ORDER BY message_idnr ASC;

[...]
> -> Bitmap Heap Scan on dbmail_messages msg
> (cost=145.12..3330.99 rows=4650 width=16) (actual time=50.689..60.132
> rows=5228 loops=1)
> Recheck Cond: (mailbox_idnr = 3236)
> Filter: ((message_idnr >= 3178782) AND (message_idnr <=
> 3616157) AND (status = ANY ('{0,1,2}'::integer[])))
> -> Bitmap Index Scan on dbmail_messages_7
> (cost=0.00..143.96 rows=5288 width=0) (actual time=50.628..50.628
> rows=15759 loops=1)
> Index Cond: (mailbox_idnr = 3236)
> -> Hash (cost=4008.37..4008.37 rows=230637 width=8) (actual
> time=669.219..669.219 rows=229809 loops=1)
[...]

> So while with the _1 index an "Index Scan" is used, with the _7 it needs
> a "Bitmap Index Scan" plus a "Bitmap Heap Scan". Can somebody explain
> why the _1 index cannot be deleted without loosing performance? The
> plain "Index Scan" could be used with _7 or _8 anyway.

Seems like the problem is that it is not pushing the "status IN"
condition as part of the index condition for some reason, and instead
using it as a filter. Maybe something to do with the selectivity of
that clause?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support