Mark Felder writes:
Suggested indexes I manually added:
I'm very puzzled by these. Do you have any insight into what queries
benefited?
CREATE INDEX CONCURRENTLY address_fields_address_fkey_idx ON
public.address_fields USING BTREE (address);
I completely understand this one, though.
I feel like the performance has improved when using a client
with no caching enabled (e.g., Roundcube) but I was also chasing
another problem where all my mails in a backup IMAP account I
sync to were not ordered correctly. The fix in a client like
Roundcube was to change the sort order from "None" to "Sent
date", but this made loading and paging through the mailbox very
slow. Postgres would hit 100% CPU and Aox would also use a lot
of CPU but also consume a lot of RAM (gigabytes!). I think it
was fetching the entire folder each time and trying to sort them
all in memory. (A folder where this is obvious has only 22,000
emails in it.)
I can guess what Roundcube does.
There's an IMAP extension called SORT, which I really dislike. Abhijit or I
tried to implement it, there was some bug, and I think I eventually removed
it from the list of capabilities. I'm sure it's fixable.
Roundcube probably doesn't use it in this case. The best way forward is to
fix sort well enough for Roundcube.
Back to the problem described earlier: I can work around the
need to use the "Sent by" sorting if I export the entire folder
to mbox, use Mutt to write a new mbox with the messages sorted
by date, and then re-importing the messages. Not ideal if you
want to fix this across many email folders but it works. (I may
have to blame imapsync for causing this problem in the first
place...)
You want the messages sorted by the value of the Date field, right?
Persistently?
Arnt
