Hi,
I reinstall all my mail server on a new box with RAID-1 and two times more
CPUs, RAM.
The speed is the same...
I know my mailbox is really large.
JG
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
--------+----------
570972 | 10039036
(1 row)
Time: 11075.192 ms
mail=# explain (verbose, analyze, buffers) 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;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=49665.61..49665.63 rows=1 width=4) (actual
time=10992.057..10992.059 rows=1 loops=1)
Output: count(*), COALESCE((sum(m.rfc822size) / 1024), 0::bigint)
Buffers: shared hit=7776, temp read=1884 written=1882
-> Hash Join (cost=19474.74..47861.28 rows=360867 width=4) (actual
time=2719.116..9858.409 rows=570972 loops=1)
Output: m.rfc822size
Hash Cond: (mm.message = m.id)
Buffers: shared hit=7776, temp read=1884 written=1882
-> Hash Join (cost=25.45..16436.74 rows=360867 width=4) (actual
time=2.672..3643.355 rows=570972 loops=1)
Output: mm.message
Hash Cond: (mm.mailbox = mb.id)
Buffers: shared hit=4471
-> Seq Scan on aox.mailbox_messages mm (cost=0.00..10527.63
rows=606663 width=8) (actual time=0.020..1110.158 rows=606256 loops=1)
Output: mm.mailbox, mm.uid, mm.message, mm.modseq,
mm.seen, mm.deleted
Buffers: shared hit=4461
-> Hash (cost=19.69..19.69 rows=461 width=4) (actual
time=2.610..2.610 rows=461 loops=1)
Output: mb.id
Buckets: 1024 Batches: 1 Memory Usage: 17kB
Buffers: shared hit=10
-> Seq Scan on aox.mailboxes mb (cost=0.00..19.69
rows=461 width=4) (actual time=0.018..1.359 rows=461 loops=1)
Output: mb.id
Filter: (mb.owner = 1)
Rows Removed by Filter: 314
Buffers: shared hit=10
-> Hash (cost=9418.46..9418.46 rows=611346 width=8) (actual
time=2714.431..2714.431 rows=610882 loops=1)
Output: m.rfc822size, m.id
Buckets: 65536 Batches: 2 Memory Usage: 11934kB
Buffers: shared hit=3305, temp written=1044
-> Seq Scan on aox.messages m (cost=0.00..9418.46 rows=611346
width=8) (actual time=0.033..1261.053 rows=610882 loops=1)
Output: m.rfc822size, m.id
Buffers: shared hit=3305
Total runtime: 11010.141 ms
(31 rows)
