I've been paying close attention to Aox lately (more on that later), and when analyzing the database with the helpful postgres_dba tool (https://github.com/NikolayS/postgres_dba) and it suggested additional indexes were needed based on the analysis picked up from pg_stat_statements. It also suggested one of the indexes was questionable.
Suggested indexes I manually added: CREATE INDEX CONCURRENTLY header_fields_field_idx ON public.header_fields USING btree (field); CREATE INDEX CONCURRENTLY deleted_messages_deleted_by_fkey_idx ON public.deleted_messages USING BTREE (deleted_by); CREATE INDEX CONCURRENTLY flags_flag_fkey_idx ON public.flags USING BTREE (flag); CREATE INDEX CONCURRENTLY messages_thread_root_fkey_idx ON public.messages USING BTREE (thread_root); CREATE INDEX CONCURRENTLY address_fields_address_fkey_idx ON public.address_fields USING BTREE (address); Questionable index that I dropped, which can be recreated with this: CREATE UNIQUE INDEX header_fields_message_part_position_field_key ON public.header_fields USING btree (message, part, "position", field); 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.) There's a single index on the date_fields table of which the DDL is: create index df_m on public.date_fields (message); I'm not sure if this index is being missed when sorting by the "Sent date". I have to capture the raw query being used here and see what's going on. Additionally there's an option in Roudcube to sort by the "Arrival date" which has the fast performance I expect. 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...) Mark
