Here's my attempt at running those queries. They seem to return much
faster than yours did, though my count is lower:
archiveopteryx=> select count(*) as c, count(rfc822size), count(m.id),
archiveopteryx-> coalesce(sum(rfc822size::bigint),0)::bigint/1024 as s
archiveopteryx-> from messages m
archiveopteryx-> join mailbox_messages mm on (m.id=mm.message)
archiveopteryx-> join mailboxes mb on (mm.mailbox=mb.id)
archiveopteryx-> where mb.owner=1;
c | count | count | s
--------+--------+--------+---------
157899 | 157899 | 157899 | 1099073
(1 row)
Time: 311.055 ms
archiveopteryx=> select count(*) as c, count(rfc822size),
archiveopteryx-> coalesce(sum(rfc822size::bigint),0)::bigint/1024 as s
archiveopteryx-> from (select rfc822size from messages m
archiveopteryx(> join mailbox_messages mm on (m.id=mm.message)
archiveopteryx(> join mailboxes mb on (mm.mailbox=mb.id)
archiveopteryx(> where mb.owner=1 group by m.id) t;
c | count | s
--------+--------+---------
157893 | 157893 | 1099057
(1 row)
Time: 369.242 ms
archiveopteryx=> select count(*) as c,
archiveopteryx-> coalesce(sum(rfc822size)/1024,0) as s
archiveopteryx-> from messages m
archiveopteryx-> join mailbox_messages mm on (m.id=mm.message)
archiveopteryx-> join mailboxes mb on (mm.mailbox=mb.id)
archiveopteryx-> where mb.owner=1;
c | s
--------+---------
157899 | 1099073
(1 row)
Time: 217.906 ms
OS: FreeBSD 9.1, PostgreSQL 9.2.2, and the database is on ZFS with
compression=on and atime=off. I do have an SSD being used for L2ARC.
