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).
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.
>>>Time to buy new hardware Thomas:
>>
>>That's not what I wanted hear :-)
>
> Nobody wants to hear that :)
And there is a cheaper and faster solution :)
>>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):
explain analyze SELECT seen_flag, answered_flag, deleted_flag,
flagged_flag, draft_flag, recent_flag, TO_CHAR(internal_date,
'YYYY-MM-DD HH24:MI:SS' ), rfcsize, message_idnr FROM dbmail_messages
msg, dbmail_physmessage pm WHERE pm.id = msg.physmessage_id AND
message_idnr BETWEEN '1' AND '500000' AND mailbox_idnr = '82' AND status
< '2' ORDER BY message_idnr ASC;
-> Sort (cost=21912.37..21921.46 rows=3637 width=36) (actual
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:
Sort (cost=562.03..562.03 rows=1 width=36) (actual
time=1345.991..1348.735 rows=811 loops=1)
Sort Key: msg.message_idnr
-> Nested Loop (cost=0.00..562.02 rows=1 width=36) (actual
time=231.209..1328.342 rows=811 loops=1)
-> Index Scan using dbmail_messages_tmm_status_recent_idx on
dbmail_messages msg (cost=0.00..555.99 rows Index Cond:
((mailbox_idnr = 82::bigint) AND (status < 2::smallint))
Filter: ((message_idnr >= 1::bigint) AND (message_idnr <=
500000::bigint))
-> Index Scan using physmessage_pkey on dbmail_physmessage pm
(cost=0.00..6.01 rows=1 width=24) (actual
Index Cond: (pm.id = "outer".physmessage_id)
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 (!!!).
The 7.4.7 query is faster than it was before too. In the last weeks I
did a kernel security upgrade so I had to reboot - strange.
Thomas
--
http://www.tmueller.com for pgp key (95702B3B)
