On Sun, 2005-05-01 at 22:27 +0200, Thomas Mueller wrote: > Hi Geo, > > sorry for the long delay, I've been very busy and had some problems with > the new clustering feature of the Debian package (some are still left).
No problems at all :) [[ looking ahead ]] I'm glad this is all getting resolved. _MY_ usage of dbmail may be single-user sqlite installations, but I'm very interested in having it scale... > I've installed a PostgreSQL 8.0.1 in a chroot, converted the 7.4.7 > configs as far as possible, did a pg_dumpall of 7.4 and imported that in > 8.0. > > The first strange thing is: the data/ directory of 7.4 has nearly 8 gig, > the dump has 4 gig, the data/ directory of 8.0 has 2.8 gig but > everything seems to be there. It sounds to me like you've got superfluous indexes, dead data, uncommitted logs, or something along those lines. When I switched to 8 from 7.4 I never saw anything like this. It's a little faster (subjective), but nothing solid. As it should already be evident, I'm a bit [ahem] determined about my SQL :) Then again, it's fairly easy to say to people "upgrade your broken RDBMS [EMAIL PROTECTED]@#" so if 8 really does offer these improvements under certain circumstances, maybe DBMAIL just has to recommend Pg8. > >>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? ;) > > Right. The difference is really impressive - to be honest that's not > what I've expected. > > > Query with PostgreSQL 7.4.7 (the details are in the older mails): > time=1038.073..1040.845 rows=811 loops=1) > > Took 7397.000, second 1094.611, third 1033.421, fourth 1044.284 > > > Same query with PostgreSQL 8.0.1: > Total runtime: 1351.952 ms > > second 73.160, third 72.099, fourth 72.959, fifth 70.791 > > So the cached results are about 15 times faster than with 7.4.7 (!!!). It _looks_ like the Pg optimizer is better grasping the relationship between the two message tables and using the index twice. That could explain a big win (although sadly, not as fast as what SQLite does...) It just hits me now, that your new plan shows the use of the physmessage index on the index Cond- however, our old plans did not. That meant we were walking the entire index, so I'm _guessing here_, but it's probable that your index was just too sparse. I probably would've had more problems as time went on (disk blocks moving, precache by os, etc). -- Internet Connection High Quality Web Hosting http://www.internetconnection.net/
