From: Michael Monnerie <michael.monnerie@is.it-management.at>
Organization: it-management http://it-management.at
To: pgsql-admin@postgresql.org
Subject: Why is that index not used?
Date: Wed, 21 Jan 2009 16:34:42 +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: <200901211634.42228@zmi.at>

I know I'm pedantic today but I have logs enabled to see every query=20
done, and those who are called often should be as quick as possible,=20
where they aren't - and I want to understand why...

Can somebody explain me why on the last line, "dbmail_physmessage",=20
there is a seq. scan going on and no index used? The query says "where=20
pm.id =3D msg.physmessage_id", and there's an index on physmessage_id, so=20
what's the problem? This takes 700 of the 900ms of the query, as there=20
are 230k entries in physmessage. I see that that query is parallel to=20
the index scan, but that's not a good choice I think. Wouldn't it be=20
better to wait for the results of the "Filter message_idnr AND status"=20
and then search only the fitting physmessage_id's?

EXPLAIN ANALYZE SELECT seen_flag, answered_flag, deleted_flag,=20
flagged_flag, draft_flag, recent_flag, TO_CHAR(internal_date, 'YYYY-MM-
DD HH24:MI:SS' ), rfcsize, message_idnr FROM dbmail_messages msg,=20
dbmail_physmessage pm WHERE pm.id =3D msg.physmessage_id AND message_idnr=20
BETWEEN 3196318 AND 3619184 AND mailbox_idnr =3D 3241 AND status IN=20
(0,1,2) ORDER BY message_idnr ASC;
                                                                           =
QUERY=20
PLAN
=2D------------------------------------------------------------------------=
=2D------------------------------------------------------------------------=
=2D-------------
 Sort  (cost=3D10466.09..10494.28 rows=3D11279 width=3D36) (actual=20
time=3D856.412..872.783 rows=3D13258 loops=3D1)
   Sort Key: msg.message_idnr
   Sort Method:  quicksort  Memory: 1420kB
   ->  Hash Join  (cost=3D6880.89..9706.93 rows=3D11279 width=3D36) (actual=
=20
time=3D702.001..822.022 rows=3D13258 loops=3D1)
         Hash Cond: (msg.physmessage_id =3D pm.id)
         ->  Index Scan using dbmail_messages_1 on dbmail_messages msg =20
(cost=3D0.00..2550.96 rows=3D11920 width=3D28) (actual time=3D0.123..30.881=
=20
rows=3D13258 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[])))
         ->  Hash  (cost=3D4004.84..4004.84 rows=3D230084 width=3D24) (actu=
al=20
time=3D701.458..701.458 rows=3D229876 loops=3D1)
               ->  Seq Scan on dbmail_physmessage pm =20
(cost=3D0.00..4004.84 rows=3D230084 width=3D24) (actual time=3D0.015..319.3=
95=20
rows=3D229876 loops=3D1)
 Total runtime: 897.722 ms

\d dbmail_physmessage;
 id            | bigint                      | not null default=20
nextval('dbmail_physmessage_id_seq'::regclass)
 messagesize   | bigint                      | not null default=20
(0)::bigint
 rfcsize       | bigint                      | not null default=20
(0)::bigint
 internal_date | timestamp without time zone |
Indexe:
    =C2=BBdbmail_physmessage_pkey=C2=AB PRIMARY KEY, btree (id) CLUSTER


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