Re: dbmail 3.1.15 slow query

Although I don't have an answer, I can confirm the slow query happens on 
3.2.1, though for me at least unrepeatable, subsequent runs of the query 
are returned sub second.

The query planner shows nothing unusual, also table stats for each of 
the query tables shows few table scans.  Fortunately it hasn't affected 
me enough to investigate further.

I haven't tried header_cache_readonly which may help.

Currently running PostgreSQL 9.2, previously saw more on MySQL 5.1 
though no stats to compare fairly.

Regards,
Alan

On 24/11/2014 10:51, Daniel Schütze wrote:
>
> I’ve noticed in my slow query log there is a particular type of query 
> which is regularly showing up and I was wondering what was triggering it.
>
> The most recent example is this (but it’s being going on since I 
> turned the slow query log on in July:
>
> SELECT m.message_idnr, n.headername, v.headervalue, CASE WHEN 
> n.headername='from' THEN 0 WHEN n.headername='to' THEN 1 WHEN 
> n.headername='cc' THEN 2 WHEN n.headername='bcc' THEN 3 WHEN 
> n.headername='subject' THEN 4 WHEN n.headername='date' THEN 5 WHEN 
> n.headername='message-id' THEN 6 WHEN n.headername='priority' THEN 7 
> WHEN n.headername='x-priority' THEN 8 WHEN n.headername='references' 
> THEN 9 WHEN n.headername='newsgroups' THEN 10 WHEN 
> n.headername='in-reply-to' THEN 11 WHEN n.headername='content-type' 
> THEN 12 WHEN n.headername='reply-to' THEN 13 END AS seq FROM 
> dbmail_header h LEFT JOIN dbmail_messages m ON 
> h.physmessage_id=m.physmessage_id LEFT JOIN dbmail_headername n ON 
> h.headername_id=n.id LEFT JOIN dbmail_headervalue v ON 
> h.headervalue_id=v.id WHERE m.mailbox_idnr = 170 AND m.message_idnr 
> BETWEEN 5477613 AND 5479501 AND n.headername IN 
> ('from','to','cc','bcc','subject','date','message-id','priority','x-priority','references','newsgroups','in-reply-to','content-type','reply-to') 
> ORDER BY message_idnr, seq;
>
> Which slow reports as taking some 33 seconds
>
> # Time: 141124 10:32:05
>
> # User@Host: dbmail[dbmail] @ localhost []
>
> # Query_time: 33.363653  Lock_time: 0.000014 Rows_sent: 0  
> Rows_examined: 20214893
>
> I’m still using dbmail 3.1.15 so apologies if this is obsolete by the 
> current 3.2.1 release; but I’m wondering if this query/speed is normal 
> or a sign something is a miss.
>
>
> Regards
>
>
> Daniel Schütze
>
> ------------------------
>
> CWA International
>
> Balmoral House
>
> 9 John Street
>
> London
> WC1N 2ES
>
> (t) + 44 (0)20 7242 8444
>
> (e) [email protected]
>
> (w) http://www.cwa.uk.com/
>
>
>
> _______________________________________________
> DBmail mailing list
> [email protected]
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

-- 
Persistent Objects Ltd
128 Lilleshall Road
Morden SM4 6DR

The Home of Lasting Solutions

Mobile: 079 3030 5004
Tel:    020 8544 5292
Web:    p-o.co.uk
Skype:  alan-hicks-london
Personal blog https://plus.google.com/+AlanHicksLondon
Company blog https://plus.google.com/+PoCoUkLondon/posts
LinkedIn https://uk.linkedin.com/in/alanhickslondon/
GitHub https://github.com/alan-hicks

_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail