Paul J Stevens wrote: > > What do you mean by 'moving messages between IMAP'. Imapsync, perhaps? >
Yikes. Didn't proofread that part. I meant to say 'moving messages between IMAP folders'. That's where the bottleneck is. Paul J Stevens wrote: > > From memory: yes, imap search requires substring matching, non-case > sensitive (charset depending). Limiting the search to the first 255 > characters is a hack to try and use a substring index > If it requires substring matching I think we're stuck. I haven't seen a reliable way to get PostgreSQL to use indexes when doing these kinds of queries unless you manually change the ENABLE_SEQSCAN parameter. That really doesn't provide any benefit when the query planner can't think of a good way to use your index in the first place. Paul J Stevens wrote: > > CREATE INDEX dbmail_headervalue_3 ON > dbmail_headervalue(substring(headervalue,0,255)); > > in order to avoid full table scans on the headervalue column. > I just added that index and the query plan still says that it is doing a sequential scan on that field. I think that without removing the ILIKE regex parts it will always be forced to do a full scan. Tim Mattison -- View this message in context: http://old.nabble.com/Performance-issue-when-copying-moving-messages-in-PostgreSQL-%28IMAP%29-tp26849342p26855351.html Sent from the dbmail dev mailing list archive at Nabble.com. _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
