On Freitag 13 Februar 2009 Paul J Stevens wrote: > It didn't get lost. I'm eagerly awaiting patches suitable for merging
Yeah, me too :-)) But there are open questions that need an answer before any patch can be thought of. Paul, you know the code, so I guess you can answer these. Example: *Side note*: Aren't most searches of the form "%searchstring%"? If yes, the index is useless, as you can only search "searchstring%" to use the index. And if the index can't be used, it could be dropped altogether. I did some searches with Kmail, the string I searched for shows what I told Kmail to search (ZMI_*, e.g. ZMI_SUBJECT searched for subject) [snip] 2) Every single search was of the form "%searchtext%", so no index could be used. I mean, in dbmail 2.2 the index »dbmail_headervalue_3« btree ("substring"(headervalue, 0, 255)) was never actually used and is therefore useless. And I guess 99% of all searches will be performed with "where X contains Y", and that's why it will have to be searched as X ILIKE %Y%. We could drop the index and the speed wil be increased, as a never used index only costs performance. Or is there any client that performs a search like Y%? I guess no. At least, when I search for messages I always search "where X contains Y" and not "where X begins with Y". 3) Searches are performed only on substring(headervalue,0,255). The whole "headervalue" is never actually searched, and nobody seems to have found that. This means either a) people don't search in such long headervalues or b) people were searching but not finding messages and wondering, still they didn't report because they can't believe the computer is wrong. Is it true that we only search the first 255 chars anyway? Then get rid of the longer headervalues. If we want to search >255 chars in headers: is the index useful at all? If %search% is used all the time, no index can be used and therefore be dropped. Starting from PostgreSQL 8.3, a full text index could be used http://www.postgresql.org/docs/8.3/static/textsearch.html But I guess we don't want database specific searches? mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4 _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://twister.fastxs.net/mailman/listinfo/dbmail-dev