On Dienstag, 19. Juni 2007 Paul J Stevens wrote: > Maybe because you were doing running the analysis queries on the > headervalue table at the time?
I thought you might answer this, but forgot to mention in the previous mail
that those lines were before my analysis. :-)
But no, even running it two times within a minute at a time without any load,
it takes it's time:
Jun 20 04:55:44 db.zmi.at postgres[15595]: [2-1] 2007-06-20 04:55:44 CEST
DB=dbmail HOST=[local] SESSTRT=2007-06-20 04:44:33 CEST LOG: Dauer: 14480.105
ms Anweisung: SELECT
Jun 20 04:55:44 db.zmi.at postgres[15595]: [2-2] message_idnr FROM
dbmail_messages m JOIN dbmail_physmessage p ON m.physmessage_id=p.id JOIN
dbmail_headervalue v ON
Jun 20 04:55:44 db.zmi.at postgres[15595]: [2-3] v.physmessage_id=p.id JOIN
dbmail_headername n ON v.headername_id=n.id WHERE mailbox_idnr = 711 AND status
IN (0,1) AND
Jun 20 04:55:44 db.zmi.at postgres[15595]: [2-4] headername ILIKE 'MESSAGE-ID'
AND headervalue ILIKE '%<[EMAIL PROTECTED]>%' ORDER BY
Jun 20 04:55:44 db.zmi.at postgres[15595]: [2-5] message_idnr;
Jun 20 04:56:32 db.zmi.at postgres[15595]: [3-1] 2007-06-20 04:56:32 CEST
DB=dbmail HOST=[local] SESSTRT=2007-06-20 04:44:33 CEST LOG: Dauer: 12370.090
ms Anweisung: SELECT
Jun 20 04:56:32 db.zmi.at postgres[15595]: [3-2] message_idnr FROM
dbmail_messages m JOIN dbmail_physmessage p ON m.physmessage_id=p.id JOIN
dbmail_headervalue v ON
Jun 20 04:56:32 db.zmi.at postgres[15595]: [3-3] v.physmessage_id=p.id JOIN
dbmail_headername n ON v.headername_id=n.id WHERE mailbox_idnr = 711 AND status
IN (0,1) AND
Jun 20 04:56:32 db.zmi.at postgres[15595]: [3-4] headername ILIKE 'MESSAGE-ID'
AND headervalue ILIKE '%<[EMAIL PROTECTED]>%' ORDER BY
Jun 20 04:56:32 db.zmi.at postgres[15595]: [3-5] message_idnr;
Running EXPLAIN on above command, I get
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Sort (cost=146016.92..146016.93 rows=1 width=8)
Sort Key: m.message_idnr
-> Nested Loop (cost=0.00..146016.91 rows=1 width=8)
-> Nested Loop (cost=0.00..146013.60 rows=1 width=24)
-> Nested Loop (cost=0.00..146010.57 rows=1 width=32)
-> Seq Scan on dbmail_headervalue v
(cost=0.00..146006.14 rows=1 width=16)
Filter: (headervalue ~~* '%<[EMAIL
PROTECTED]>%'::text)
-> Index Scan using dbmail_messages_2 on dbmail_messages
m (cost=0.00..4.42 rows=1 width=16)
Index Cond: ("outer".physmessage_id =
m.physmessage_id)
Filter: ((mailbox_idnr = 711) AND ((status = 0) OR
(status = 1)))
-> Index Scan using dbmail_headername_pkey on dbmail_headername
n (cost=0.00..3.02 rows=1 width=8)
Index Cond: ("outer".headername_id = n.id)
Filter: ((headername)::text ~~* 'MESSAGE-ID'::text)
-> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage p
(cost=0.00..3.30 rows=1 width=8)
Index Cond: ("outer".physmessage_id = p.id)
Seems to me the problem is the relation of physmessage -> headervalue ->
headername
If that would be physmessage -> headername -> headervalue, there would
be a dramatic speedup, because the planner could lookup all
headername='message-id' before having to look at the headervalues. This is
especially true when you look at the number of rows:
select count(*) from dbmail_headername;
2430
dbmail=> select count(*) from dbmail_headervalue;
4979350
Any chance this could be changed? Or are there many lookups that
search first on headervalue and only afterwards on headername?
mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: EA39 8918 EDFF 0A68 ACFB 11B7 BA2D 060F 1C6F E6B0
// Keyserver: www.keyserver.net Key-ID: 1C6FE6B0
signature.asc
Description: This is a digitally signed message part.
_______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
