Date: Wed, 21 Jan 2009 11:28:29 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Michael Monnerie <michael.monnerie@is.it-management.at>
Cc: pgsql-admin@postgresql.org
Subject: Re: Question on Index usage
Message-ID: <20090121142829.GE4038@alvh.no-ip.org>
References: <200901210854.28091@zmi.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200901210854.28091@zmi.at>
User-Agent: Mutt/1.5.18 (2008-05-17)

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