I know I'm pedantic today but I have logs enabled to see every query
done, and those who are called often should be as quick as possible,
where they aren't - and I want to understand why...
Can somebody explain me why on the last line, "dbmail_physmessage",
there is a seq. scan going on and no index used? The query says "where
pm.id = msg.physmessage_id", and there's an index on physmessage_id, so
what's the problem? This takes 700 of the 900ms of the query, as there
are 230k entries in physmessage. I see that that query is parallel to
the index scan, but that's not a good choice I think. Wouldn't it be
better to wait for the results of the "Filter message_idnr AND status"
and then search only the fitting physmessage_id's?
EXPLAIN ANALYZE SELECT seen_flag, answered_flag, deleted_flag,
flagged_flag, draft_flag, recent_flag, TO_CHAR(internal_date, 'YYYY-MM-
DD HH24:MI:SS' ), rfcsize, message_idnr FROM dbmail_messages msg,
dbmail_physmessage pm WHERE pm.id = msg.physmessage_id AND message_idnr
BETWEEN 3196318 AND 3619184 AND mailbox_idnr = 3241 AND status IN
(0,1,2) ORDER BY message_idnr ASC;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=10466.09..10494.28 rows=11279 width=36) (actual
time=856.412..872.783 rows=13258 loops=1)
Sort Key: msg.message_idnr
Sort Method: quicksort Memory: 1420kB
-> Hash Join (cost=6880.89..9706.93 rows=11279 width=36) (actual
time=702.001..822.022 rows=13258 loops=1)
Hash Cond: (msg.physmessage_id = pm.id)
-> Index Scan using dbmail_messages_1 on dbmail_messages msg
(cost=0.00..2550.96 rows=11920 width=28) (actual time=0.123..30.881
rows=13258 loops=1)
Index Cond: (mailbox_idnr = 3241)
Filter: ((message_idnr >= 3196318) AND (message_idnr <=
3619184) AND (status = ANY ('{0,1,2}'::integer[])))
-> Hash (cost=4004.84..4004.84 rows=230084 width=24) (actual
time=701.458..701.458 rows=229876 loops=1)
-> Seq Scan on dbmail_physmessage pm
(cost=0.00..4004.84 rows=230084 width=24) (actual time=0.015..319.395
rows=229876 loops=1)
Total runtime: 897.722 ms
\d dbmail_physmessage;
id | bigint | not null default
nextval('dbmail_physmessage_id_seq'::regclass)
messagesize | bigint | not null default
(0)::bigint
rfcsize | bigint | not null default
(0)::bigint
internal_date | timestamp without time zone |
Indexe:
»dbmail_physmessage_pkey« PRIMARY KEY, btree (id) CLUSTER
mfg zmi
--
// 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
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
