Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id D4125632571
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jan 2009 10:22:27 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 20183-02-5
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jan 2009 10:22:22 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mailsrv1.zmi.at (mailsrv1.zmi.at [212.69.162.198])
	by mail.postgresql.org (Postfix) with ESMTP id 11EE8632335
	for <pgsql-admin@postgresql.org>; Wed, 21 Jan 2009 10:21:06 -0400 (AST)
Received: from mailsrv2.i.zmi.at (unknown [81.217.54.243])
	(using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits))
	(Client CN "mailsrv2.i.zmi.at", Issuer "power4u.zmi.at" (not verified))
	by mailsrv1.zmi.at (Postfix) with ESMTP id D3E962ADF
	for <pgsql-admin@postgresql.org>; Wed, 21 Jan 2009 08:54:27 +0100 (CET)
Received: from saturn.localnet (saturn.i.zmi.at [10.0.0.2])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(Client did not present a certificate)
	by mailsrv2.i.zmi.at (Postfix) with ESMTPSA id 61944400155
	for <pgsql-admin@postgresql.org>; Wed, 21 Jan 2009 08:54:28 +0100 (CET)
From: Michael Monnerie <michael.monnerie@is.it-management.at>
Organization: it-management http://it-management.at
To: pgsql-admin@postgresql.org
Subject: Question on Index usage
Date: Wed, 21 Jan 2009 08:54:27 +0100
User-Agent: KMail/1.10.3 (Linux/2.6.27.10-ZMI; KDE/4.1.3; x86_64; ; )
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200901210854.28091@zmi.at>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200901/82
X-Sequence-Number: 31129

http://www.postgresql.org/docs/8.3/interactive/using-explain.html

I tried reading that page, but it's still not clear to me, why the index=20
dbmail_messages_1 is better than dbmail_messages_7:

\d dbmail_messages
                                    Tabelle =C2=BBpublic.dbmail_messages=C2=
=AB
     Spalte     |          Typ          |                          =20
Attribute
=2D---------------+-----------------------+--------------------------------=
=2D------------------------------
 message_idnr   | bigint                | not null default=20
nextval('dbmail_message_idnr_seq'::regclass)
 mailbox_idnr   | bigint                |
 physmessage_id | bigint                |
<snip other colums not important>
Indexe:
    =C2=BBdbmail_messages_pkey=C2=AB PRIMARY KEY, btree (message_idnr)
    =C2=BBdbmail_messages_1=C2=AB btree (mailbox_idnr)
    =C2=BBdbmail_messages_2=C2=AB btree (physmessage_id)
    =C2=BBdbmail_messages_3=C2=AB btree (seen_flag)
    =C2=BBdbmail_messages_4=C2=AB btree (unique_id)
    =C2=BBdbmail_messages_5=C2=AB btree (status)
    =C2=BBdbmail_messages_6=C2=AB btree (status) WHERE status < 2::smallint
    =C2=BBdbmail_messages_7=C2=AB btree (mailbox_idnr, status, seen_flag) C=
LUSTER
    =C2=BBdbmail_messages_8=C2=AB btree (mailbox_idnr, status, recent_flag)

Then I do a SELECT that joins this with another table:

EXPLAIN ANALYZE SELECT 1 FROM dbmail_messages msg JOIN=20
dbmail_physmessage pm ON ( pm.id =3D msg.physmessage_id ) WHERE=20
message_idnr BETWEEN 3178782 AND 3616157 AND mailbox_idnr =3D 3236 AND=20
status IN (0,1,2) ORDER BY message_idnr ASC;
                                                                          Q=
UERY=20
PLAN
=2D------------------------------------------------------------------------=
=2D------------------------------------------------------------------------=
=2D-----------
 Sort  (cost=3D8593.80..8605.30 rows=3D4599 width=3D8) (actual=20
time=3D707.878..713.738 rows=3D5228 loops=3D1)
   Sort Key: msg.message_idnr
   Sort Method:  quicksort  Memory: 438kB
   ->  Hash Join  (cost=3D6891.33..8314.02 rows=3D4599 width=3D8) (actual=20
time=3D667.133..697.308 rows=3D5228 loops=3D1)
         Hash Cond: (msg.physmessage_id =3D pm.id)
         ->  Index Scan using dbmail_messages_1 on dbmail_messages msg =20
(cost=3D0.00..1324.96 rows=3D4599 width=3D16) (actual time=3D0.094..10.958=
=20
rows=3D5228 loops=3D1)
               Index Cond: (mailbox_idnr =3D 3236)
               Filter: ((message_idnr >=3D 3178782) AND (message_idnr <=3D=
=20
3616157) AND (status =3D ANY ('{0,1,2}'::integer[])))
         ->  Hash  (cost=3D4008.37..4008.37 rows=3D230637 width=3D8) (actua=
l=20
time=3D666.628..666.628 rows=3D229809 loops=3D1)
               ->  Seq Scan on dbmail_physmessage pm =20
(cost=3D0.00..4008.37 rows=3D230637 width=3D8) (actual time=3D0.012..303.13=
9=20
rows=3D229809 loops=3D1)
 Total runtime: 729.972 ms

Good, and now I
DROP INDEX dbmail_messages_1;
because anyway there are the _7 and _8 indices which both have=20
mailbox_idnr as their first column, so sorting is the same as in the _1=20
index. And _7 is even used by CLUSTER. But that makes
                                                                   QUERY=20
PLAN
=2D------------------------------------------------------------------------=
=2D-----------------------------------------------------------------------
 Sort  (cost=3D10601.66..10613.20 rows=3D4615 width=3D8) (actual=20
time=3D759.415..765.292 rows=3D5228 loops=3D1)
   Sort Key: msg.message_idnr
   Sort Method:  quicksort  Memory: 438kB
   ->  Hash Join  (cost=3D7036.45..10320.79 rows=3D4615 width=3D8) (actual=
=20
time=3D720.352..748.912 rows=3D5228 loops=3D1)
         Hash Cond: (msg.physmessage_id =3D pm.id)
         ->  Bitmap Heap Scan on dbmail_messages msg =20
(cost=3D145.12..3330.99 rows=3D4650 width=3D16) (actual time=3D50.689..60.1=
32=20
rows=3D5228 loops=3D1)
               Recheck Cond: (mailbox_idnr =3D 3236)
               Filter: ((message_idnr >=3D 3178782) AND (message_idnr <=3D=
=20
3616157) AND (status =3D ANY ('{0,1,2}'::integer[])))
               ->  Bitmap Index Scan on dbmail_messages_7 =20
(cost=3D0.00..143.96 rows=3D5288 width=3D0) (actual time=3D50.628..50.628=20
rows=3D15759 loops=3D1)
                     Index Cond: (mailbox_idnr =3D 3236)
         ->  Hash  (cost=3D4008.37..4008.37 rows=3D230637 width=3D8) (actua=
l=20
time=3D669.219..669.219 rows=3D229809 loops=3D1)
               ->  Seq Scan on dbmail_physmessage pm =20
(cost=3D0.00..4008.37 rows=3D230637 width=3D8) (actual time=3D0.014..301.79=
3=20
rows=3D229809 loops=3D1)
 Total runtime: 781.238 ms

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

mfg zmi
=2D-=20
// 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