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
Date: Wed, 21 Jan 2009 11:28:29 -0300
Message-ID: <20090121142829.GE4038@alvh.no-ip.org> (raw)
In-Reply-To: <200901210854.28091@zmi.at>
References: <200901210854.28091@zmi.at>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: alvherre@commandprompt.com, michael.monnerie@is.it-management.at
Subject: Re: Question on Index usage
In-Reply-To: <20090121142829.GE4038@alvh.no-ip.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox