From: Michael Monnerie <michael.monnerie@is.it-management.at>
Organization: it-management http://it-management.at
To: pgsql-admin@postgresql.org
Subject: Re: Why is that index not used?
Date: Fri, 23 Jan 2009 13:57:07 +0100
User-Agent: KMail/1.10.3 (Linux/2.6.27.10-ZMI; KDE/4.1.3; x86_64; ; )
References: <200901211634.42228@zmi.at> <2612.1232553100@sss.pgh.pa.us>
In-Reply-To: <2612.1232553100@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200901231357.07731@zmi.at>

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. =A0Given the rowcounts I don't
> think that's wrong,=20

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

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

Even worse:
# set enable_hashjoin =3D off ;
 Sort  (cost=3D12400.45..12427.22 rows=3D10707 width=3D36) (actual=20
time=3D922.751..934.921 rows=3D9935 loops=3D1)
   Sort Key: msg.message_idnr
   Sort Method:  quicksort  Memory: 1161kB
   ->  Merge Join  (cost=3D3240.39..11683.82 rows=3D10707 width=3D36) (actu=
al=20
time=3D169.998..895.009 rows=3D9935 loops=3D1)
         Merge Cond: (pm.id =3D msg.physmessage_id)
         ->  Index Scan using dbmail_physmessage_pkey on=20
dbmail_physmessage pm  (cost=3D0.00..7681.79 rows=3D230034 width=3D24) (act=
ual=20
time=3D0.026..407.428 rows=3D229873 loops=3D1)
         ->  Sort  (cost=3D3239.57..3266.75 rows=3D10874 width=3D28) (actua=
l=20
time=3D43.959..68.333 rows=3D9935 loops=3D1)
               Sort Key: msg.physmessage_id
               Sort Method:  quicksort  Memory: 1161kB
               ->  Index Scan using dbmail_messages_1 on dbmail_messages=20
msg  (cost=3D0.00..2510.54 rows=3D10874 width=3D28) (actual time=3D2.025..2=
3.155=20
rows=3D9935 loops=3D1)
                     Index Cond: (mailbox_idnr =3D 3241)
                     Filter: ((message_idnr >=3D 3196318) AND=20
(message_idnr <=3D 3619184) AND (status =3D ANY ('{0,1,2}'::integer[])))
 Total runtime: 946.016 ms

Then in addition:
# set enable_mergejoin =3D off;
 Sort  (cost=3D15878.43..15905.19 rows=3D10707 width=3D36) (actual=20
time=3D343.430..358.492 rows=3D9935 loops=3D1)
   Sort Key: msg.message_idnr
   Sort Method:  quicksort  Memory: 1161kB
   ->  Nested Loop  (cost=3D0.00..15161.79 rows=3D10707 width=3D36) (actual=
=20
time=3D2.040..314.101 rows=3D9935 loops=3D1)
         ->  Index Scan using dbmail_messages_1 on dbmail_messages msg =20
(cost=3D0.00..2510.54 rows=3D10874 width=3D28) (actual time=3D1.953..30.407=
=20
rows=3D9935 loops=3D1)
               Index Cond: (mailbox_idnr =3D 3241)
               Filter: ((message_idnr >=3D 3196318) AND (message_idnr <=3D=
=20
3619184) AND (status =3D ANY ('{0,1,2}'::integer[])))
         ->  Index Scan using dbmail_physmessage_pkey on=20
dbmail_physmessage pm  (cost=3D0.00..1.15 rows=3D1 width=3D24) (actual=20
time=3D0.016..0.018 rows=3D1 loops=3D9935)
               Index Cond: (pm.id =3D 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,=20
because they provide the same first field order, so there's no=20
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=20
index _7 with the same field, just plus additional fields. I thought=20
that would be redundant:

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

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

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