Every IMAP sync loads the whole source's UID aliases to use a handful of them

[exactmike]

What happens

imapFolderStateOptions (cmd/msgvault/cmd/syncfull.go) calls
Store.GetIMAPSourceMessageAliases once per sync, before the client knows what
it is going to fetch. The query joins every stored membership of the source to
messages to read one column:

SELECT membership.mailbox, membership.uid, messages.source_message_id
FROM imap_message_memberships membership
JOIN imap_folder_state state ...
JOIN messages ON messages.id = membership.message_id
WHERE membership.source_id = ? AND messages.source_message_id IS NOT NULL

The result is passed to the client as WithSourceMessageAliases, and it is read
at three places only, all of them per-message and only for messages the run is
actually fetching:

• applyFetchResults (internal/imap/batch_fetch.go), for a message with no
  Message-ID header in a secondary mailbox
• tryBuildQresyncMessageList (internal/imap/qresync.go), for the UIDs
  QRESYNC reports as changed
• buildMessageListCache (internal/imap/client.go), for the UIDs a mailbox
  listing produced

An incremental sync fetches a handful of messages, so nearly the whole map is
built and thrown away. One scheduled run here spent 2,865 ms of a 3-second sync
in this load, and fetched one message.

Numbers

Office 365 account: 40 mailboxes, ~118k messages, 12 GB SQLite vault.
118,159 alias rows. Each timing is a fresh process:

query	rows	cold page cache	warm
whole source, as it runs today	118,159	3.010 s	0.139 s
the UIDs one incremental run lists	1	—	0.002 s

Cold and warm differ by 20x, so the cost of the load depends on whether the
12 GB messages table is still resident. A 15-minute sync interval does not
keep it resident.

Measured inside a real sync, not only as a standalone query. On a copy of that
vault, with the database file's page cache dropped first:

run	alias queries	rows requested	time in them
nothing to fetch	1	118,263 (all)	423 ms
ten messages to examine, across 4 of 40 mailboxes	1	118,127 (all)	470 ms

The second run is the ordinary case: three forwarded messages arrived, several
were deleted, and one was moved between mailboxes. The run needed the aliases
of ten UIDs and read every one the account has.

The query plan is healthy and needs no new index:

SEARCH state USING INDEX sqlite_autoindex_imap_folder_state_1 (source_id=? AND mailbox=?)
SEARCH membership USING INDEX sqlite_autoindex_imap_message_memberships_1 (source_id=? AND mailbox=? AND uidvalidity=?)
SEARCH messages USING INTEGER PRIMARY KEY (rowid=?)

The time is 118k random row lookups into a 12 GB table. A covering index on
messages(id, source_message_id) does not change the plan: SQLite keeps the
rowid lookup. Tested on a padded synthetic copy.

Fix

Ask for the aliases of the UIDs the run lists, instead of every alias the
source has. The existing primary key
(source_id, mailbox, uidvalidity, uid) already serves that query. The client
option becomes a loader, because imapFolderStateOptions runs before the scan
plan exists and cannot know the UIDs yet.

PR: #746

Environment

msgvault built from main at 9938965a, Linux, SQLite backend, IMAP source
against Office 365.

[salmonumbrella]

@exactmike thanks for the issues and PRs you've been making!