On Mittwoch, 20. Juni 2007 Paul J Stevens wrote: > There is no direct relation between headernames and physmessages. So > no, I don't see how that could be changed easily. If you can whip up > a query that improves the situation please share it.
I've tried around, but postgres kept insisting on the full table scan
for headervalues, although there's a
WHERE headername ilike 'bla'
that would reduce the search a lot. So I made
create index dbmail_headervalue_2 on dbmail_headervalue using btree
(headername_id );
and this reduced query time to half the time used:
explain analyze select message_idnr from dbmail_headername n JOIN
dbmail_headervalue v ON v.headername_id=n.id
JOIN dbmail_physmessage p on v.physmessage_id=p.id join dbmail_messages m on
m.physmessage_id=p.id
WHERE mailbox_idnr = 711 AND status IN (0,1) AND headername ILIKE 'MESSAGE-ID'
AND
headervalue ILIKE '%<[EMAIL PROTECTED]>%' ORDER BY message_idnr;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=45546.11..45546.11 rows=1 width=8) (actual time=6376.378..6376.378
rows=0 loops=1)
Sort Key: m.message_idnr
-> Nested Loop (cost=209.88..45546.10 rows=1 width=8) (actual
time=6376.360..6376.360 rows=0 loops=1)
-> Nested Loop (cost=209.88..45542.69 rows=1 width=24) (actual
time=6376.358..6376.358 rows=0 loops=1)
-> Nested Loop (cost=209.88..45538.31 rows=1 width=8) (actual
time=2466.509..6376.349 rows=1 loops=1)
-> Seq Scan on dbmail_headername n (cost=0.00..49.45
rows=1 width=8) (actual time=0.016..2.211 rows=4 loops=1)
Filter: ((headername)::text ~~* 'MESSAGE-ID'::text)
-> Bitmap Heap Scan on dbmail_headervalue v
(cost=209.88..45488.85 rows=1 width=16) (actual time=1515.248..1593.284 rows=0
loops=4)
Recheck Cond: (v.headername_id = "outer".id)
Filter: (headervalue ~~* '%<[EMAIL
PROTECTED]>%'::text)
-> Bitmap Index Scan on dbmail_headervalue_2
(cost=0.00..209.88 rows=33394 width=0) (actual time=26.614..26.614 rows=40916
loops=4)
Index Cond: (v.headername_id = "outer".id)
-> Index Scan using dbmail_messages_2 on dbmail_messages m
(cost=0.00..4.36 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=1)
Index Cond: (m.physmessage_id = "outer".physmessage_id)
Filter: ((mailbox_idnr = 711) AND ((status = 0) OR (status
= 1)))
-> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage p
(cost=0.00..3.40 rows=1 width=8) (never executed)
Index Cond: ("outer".physmessage_id = p.id)
Total runtime: 6376.441 ms
(18 Zeilen)
That query took 12-14s before, so the change is significant. Maybe people
should
create that index if they suffer from a big dbmail_headervalues table, and
it could be good for the 2.2.x series anyway.
> Yes, that is the current situation where headervalues have a direct
> relation with a physmessage. Like we discussed in the 'compressing
> header info' thread, that will be changed during the 2.3 cycle.
I'm loving it. *g*
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
