-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Yavor Shahpasov wrote:
| A005 SEARCH CHARSET UTF-8 TEXT "charsettest" 1.. | | On my system this command would take 40-50 seconds. After resolving the | above issue it went down to 12-15 seconds. | I have traced this command to the following SQL query | | | SELECT message_idnr | FROM | dbmail_messages m | JOIN dbmail_physmessage p ON m.physmessage_id=p.id <http://p.id> | JOIN dbmail_headervalue v on v.physmessage_id=p.id <http://p.id> | WHERE | mailbox_idnr = 1 | AND status IN (0,1) | AND SUBSTRING(headervalue,0,255) ILIKE '%charsettest%' ORDER BY | message_idnr | | | The search is done on a mailbox with 2k emails. These span around 45k | rows in dbmail_headervalue. I have tried modifying it to get better | performance but with no results. From my experiance with traditional | Maildir based imap server searching through 2000 emails is much faster, | given that the above searches only in the headers. Also it looks like | the index on "SUBSTRING(headervalue,0,255)" is completly ignored for the | ILIKE operator. | | I don't know ho the above can be improved, if anyone has ideas let me know. | Yavor, have you analyzed dbmail_message and dbmail_physmessage ? If the statistics were old for dbmail_headervalue, they could also be for the other tables. I've not used postgres so I am not familiar with db maintenance tasks, but I imagine with your volumes you will need to run analzye periodically otherwise stats will get out of sync. On the query above, what does an explain show? John -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.4-svn0 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFIVMzmd4I3jTtt9EIRAiVJAJ92UMCNkx3dFNr0Ne3xu4FRFrE2uwCg1JVI hQzBSpzIRcarnYs6pMalhVs= =AOGq -----END PGP SIGNATURE----- _______________________________________________ Dbmail-dev mailing list [email protected] http://twister.fastxs.net/mailman/listinfo/dbmail-dev
