On Thu, 2005-03-17 at 15:12 +0100, Thomas Mueller wrote: > Geo Carncross wrote: > > > Time to buy new hardware Thomas: > > That's not what I wanted hear :-)
Nobody wants to hear that :) > 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. [EMAIL PROTECTED] ~]# /sbin/hdparm -t /dev/sda /dev/sda: Timing buffered disk reads: 172 MB in 3.03 seconds = 56.77 MB/sec Only about 3x faster I/O. But then, you're seeing enormous increase in speed when you don't have to wait for the disk, right? > 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. I'd be very interested in this. Pg 8 looks like it has loads more code than 7.4, so it's got to be faster, right? ;) > > > 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. I'm not certain either, that is, I haven't proved it. I believe Pg doesn't understand that an index on table 1 can be used to find data on table 2 WITHOUT having to scan table 2 multiple times. Having two indexes means the scan completes faster. I'm not sure though. I'd love to hear from the Pg people about it. > > Total runtime: 1838.451 ms > > That would be great ... Can anyone find out how this performs on MySQL? [SQLite knows better and doesn't need two indexes] -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
