Hello, I found this message in my log, the query was run from dbmail-util: dbmail/maintenance[10985]: Warning:[db] dbmodule.c,db_query(+151): slow query [SELECT p.id FROM dbmail_physmessage p LEFT JOIN dbmail_headervalue h ON p.id = h.physmessage_id WHERE h.physmessage_id IS NULL] took [74] seconds
I made explain analyze SELECT p.id FROM dbmail_physmessage p LEFT JOIN
dbmail_headervalue h ON p.id = h.physmessage_id WHERE h.physmessage_id
IS NULL;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=576001.98..642824.08 rows=116125 width=8)
(actual time=167715.689..231489.237 rows=15 loops=1)
Merge Cond: ("outer".id = "inner".physmessage_id)
Filter: ("inner".physmessage_id IS NULL)
-> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage p
(cost=0.00..3987.60 rows=116125 width=8) (actual time=0.089..1689.135
rows=115520 loops=1)
-> Sort (cost=576001.98..586426.01 rows=4169613 width=8) (actual
time=161547.086..200170.129 rows=4167058 loops=1)
Sort Key: h.physmessage_id
-> Seq Scan on dbmail_headervalue h (cost=0.00..117522.13
rows=4169613 width=8) (actual time=0.445..70881.046 rows=4167058
loops=1)
Total runtime: 231785.972 ms
(8 Zeilen)
And I think this query should do the same (it at least finds the same 15
rows for me), but is considerably faster:
EXPLAIN ANALYZE SELECT p.id from dbmail_physmessage p where p.id NOT IN
(select physmessage_id from dbmail_headervalue);
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on dbmail_physmessage p (cost=127947.85..130367.41 rows=58062
width=8) (actual time=105060.736..105533.860 rows=15 loops=1)
Filter: (NOT (hashed subplan))
SubPlan
-> Seq Scan on dbmail_headervalue (cost=0.00..117523.68
rows=4169668 width=8) (actual time=1.405..75893.989 rows=4167100
loops=1)
Total runtime: 105546.817 ms
(5 Zeilen)
What do you think?
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
