Then why do I keep hearing noises on this list that JOINs are faster than
sub-SELECTS ??
Michael Monnerie wrote:
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
------------------------------------------------------------------------
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
