Hi, Le Mardi 8 Janvier 2013 21:59 CET, Arnt Gulbrandsen <[email protected]> a écrit:
> On 01/08/2013 09:54 PM, [email protected] wrote: > > Yes, that is possible. I do that often (in other software) > > What is the query ? > > q = new Query( "select count(rfc822size) as c, " > "coalesce(sum(rfc822size::bigint),0)::bigint/1024 as s " > "from messages m" > " join mailbox_messages mm on (m.id=mm.message)" > " join mailboxes mb on (mm.mailbox=mb.id)" > " where mb.owner=$1", this ) A few test on my setup... mail=# select count(*) as c, count(rfc822size), count(m.id), coalesce(sum(rfc822size::bigint),0)::bigint/1024 as s from messages m join mailbox_messages mm on (m.id=mm.message) join mailboxes mb on (mm.mailbox=mb.id) where mb.owner=1; c | count | count | s --------+--------+--------+--------- 570433 | 570433 | 570433 | 9999978 (1 row) Time: 13813.692 ms mail=# select count(*) as c, count(rfc822size), coalesce(sum(rfc822size::bigint),0)::bigint/1024 as s from (select rfc822size from messages m join mailbox_messages mm on (m.id=mm.message) join mailboxes mb on (mm.mailbox=mb.id) where mb.owner=1 group by m.id) t; c | count | s --------+--------+--------- 570292 | 570292 | 9993040 (1 row) Time: 22560.268 ms We see that I have 570292 message in my IMAP account. But I have 570431 in all the folders because some message are in two differents folders. The used size on disk is 9993040 Kb. But the approximated size, ie. the size of the 570431 messages, is 9999974 Kb. The new query you used is a good approximation of the exact size used, and we win 9 seconds (from 22,5 seconds down to 13,8 s). We see that count(*) and count(rfc822size) gives the same result. This is true, if rfc822size is not null. => Good point to approximate the size, you win 38% in time. One more optimisation: sum(int) is bigint sum(bigint) is numeric numeric is slow to compute. bigint is fast. rfc822size is int, no need to cast to bigint. mail=# select count(*) as c, coalesce(sum(rfc822size)/1024,0) as s from messages m join mailbox_messages mm on (m.id=mm.message) join mailboxes mb on (mm.mailbox=mb.id) where mb.owner=1; c | s --------+--------- 570433 | 9999978 (1 row) Time: 12294.859 ms => 1,6 seconds less. 11% less. Total is 45% less in time. JG
