Geo Carncross wrote:
> Time to buy new hardware Thomas:
That's not what I wanted hear :-)
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Merge Join (cost=0.00..726.83 rows=6245 width=36) (actual
> time=73.480..4860.933 rows=43644 loops=1)
> Merge Cond: ("outer".id = "inner".physmessage_id)
> -> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage b
> (cost=0.00..216.71 rows=6246 width=24) (actual time=14.958..1227.097
> rows=43644 loops=1)
So that one is more than 10 times faster - I can hardly believe you get
10 times more IO. Even on a busy (!) disc of the raid array 'hdparm -t'
reads 20 MB/s - so balanced reads from the raid 1 discs are even faster.
[ other mail ]
> I would dump the data you've got, recreate the databases as I did, and
> reload your data after the fact (no tables/index), and take another
> look.
I'm quite busy at the moment, but I'll try to install a PostgreSQL 8.0
in a chroot this weekend. Then I'll be able to dump and import my
database without downtime - only for speed comparison I have to shut
down my production database.
I'll report the results.
> But watch this:
[..]
> dbmail=# create index dbmail_foo on dbmail_physmessage
> (message_idnr,mailbox_idnr,id);
> dbmail=# reindex database dbmail force;
[..]
> -> Index Scan using dbmail_foo on dbmail_physmessage b
> (cost=0.00..6.38 rows=2 width=40) (actual time=0.055..197.415 rows=43644
> loops=1)
> Index Cond: ((message_idnr >= 1::bigint) AND (message_idnr <=
> 417966::bigint) AND (mailbox_idnr = 1::bigint))
I really don't understand why it is that fast.
> Total runtime: 1838.451 ms
That would be great ...
Thomas
--
http://www.tmueller.com for pgp key (95702B3B)
