On Donnerstag, 23. August 2007 Jesse Norell wrote: > try inserting the > value of either message-id or resent-message-id into it and if it's > already there the insert will fail (of course it could fail for other > reasons too..). That ought to be much quicker than a 75 second > SELECT.
No, no, no, please don't do such dirty hacking. If the database is too
slow, tune it, or tune the hardware (more RAM, faster disks,...). A
SELECT is the correct thing to do here. If it's too expensive, optimize
something - but clean.
> (Or maybe an index could help with the existing SELECT?)
That sounds like an idea. But where to start optimizing?
EXPLAIN SELECT message_idnr FROM dbmail_messages m JOIN
dbmail_physmessage p ON m.physmessage_id=p.id JOIN dbmail_headervalue v
ON v.physmessage_id=p.id JOIN dbmail_headername n ON
v.headername_id=n.id WHERE m.mailbox_idnr=2 AND n.headername IN
('resent-message-id','message-id') AND
v.headervalue='<[EMAIL PROTECTED]>'AND
p.internal_date > NOW();
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=8.13..3014.66 rows=1 width=8)
-> Nested Loop (cost=8.13..3011.62 rows=1 width=16)
-> Nested Loop (cost=0.00..1557.29 rows=1 width=24)
-> Index Scan using dbmail_messages_1 on dbmail_messages
m (cost=0.00..474.70 rows=242 width=16)
Index Cond: (mailbox_idnr = 2)
-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage p (cost=0.00..4.46 rows=1 width=8)
Index Cond: ("outer".physmessage_id = p.id)
Filter: (internal_date > now())
-> Bitmap Heap Scan on dbmail_headervalue v
(cost=8.13..1446.74 rows=608 width=16)
Recheck Cond: (v.physmessage_id = "outer".id)
Filter: (headervalue
= '<[EMAIL PROTECTED]>'::text)
-> Bitmap Index Scan on dbmail_headervalue_1
(cost=0.00..8.13 rows=608 width=0)
Index Cond: (v.physmessage_id = "outer".id)
-> 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 = 'resent-message-id'::text) OR
((headername)::text = 'message-id'::text))
(16 Zeilen)
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
