Hello,
I made an upgrade from 2.0.10 to 2.1.7 some days ago and I discoverd two
things I wanted to share with you. My server (Celeron 2.4GHz, 512 MB
RAM) ran PostgreSQL 7.4.7 and there were about 110,000 Mails in the database.
1. duplicate References headers in mails:
After the schema upgrade (which worked without problems) I ran
dbmail-util -ay
to generate the header caches etc. dbmail-util started giving errors like
Error dbpgsql.c, db_query: Error executing query [INSERT INTO
dbmail_referencesfield (physmessage_id, referencesfield) VALUES
(4933,'[EMAIL PROTECTED]')] :
[ERROR: duplicate key violates unique constraint
"dbmail_referencesfield_1"
]
Obviously, some mails have duplicate entries in the References header. I
aborted dbmail-util and changed to the dbmail_referencesfield_1 index (which
is created as "UNIQUE INDEX") _not_ to be UNIQUE which made the problem go away.
2. PostgreSQL 7.4 speed:
dbmail-util -ay run for 11 hours(!) to fix the header caches etc. and
IMAP was _very_ slow after the upgrade. Especially fetching message
headers was 10 or 100 times slower than with 2.0.10. I found out that the
problem is that the queries sent to PostgreSQL have conditions like
physmessage_id IN (573485)
in it, but PostgreSQL 7.4 can only use indexes if the condition is
written as
physmessage_id IN (753485::bigint)
Meanwhile I upgraded to PostgreSQL 8.1.4 which does not have this
problem with using indexes when types don't match exactly. IMAP is now at
least as fast as with 2.0.10.
Thanks for all the work you put into DBMail.
Regards,
Armin
