Reported: message loading is slow even on a current flagship device (Pixel 10 XL). The likely
architectural cause, from reading the mailbox list's data path:
MessageDao.observeSummaries() (MessageDao.kt:20-24) has no WHERE/LIMIT — it observes every
cached message across every account and folder, and re-emits the full result set on any write to
the messages table (a new message arriving via IDLE, a star/read-flag toggle, a sync of an unrelated
folder, …).
MailboxViewModel.messages (MailboxViewModel.kt:96-111) then runs a client-side .filter{} over that
entire cross-account/folder list on every recombination of its 4 input flows, just to get the handful
of rows for the currently-viewed account+folder. LazyColumn (MailboxScreen.kt:270-299) renders
whatever survives.
There's no Paging3/PagingSource — the whole filtered list is held and diffed in memory.
MessageEntity (MessageEntity.kt:9-12) indexes accountId and timestampMillis individually, but
not the (accountId, folder, inInbox) combination the list actually filters on.
This scales with total cached message count, not with what's visible or changed. Since the default
sync policy backfills a mailbox's entire history (#12, done) with keep-everything retention (#13, done),
a real, longstanding account's cache can grow to many thousands of rows — and every unrelated write
anywhere in messages re-triggers the full observe → filter → diff pipeline. MessageSummary's own doc
comment (MessageSummary.kt:8-11) already records a prior incident in this exact path (a SQLite
CursorWindow overflow, issue #51) caused by not bounding what the list query pulls.
Suggested approach
Push account/folder filtering into the SQL query (WHERE accountId = :accountId AND folder = :folder)
instead of MailboxViewModel's in-memory .filter{}, so Room only re-queries/re-emits when a relevant
row actually changes.
Add a composite index covering (accountId, folder, inInbox, timestampMillis) to support that query
and the existing folder-scoped DAO methods.
Consider Room PagingSource/Paging3 for the LazyColumn so both query cost and recomposition cost
scale with what's on screen, not the whole cache. Search (matchesSearch, cross-field) can stay a
separate on-demand path since it already needs different semantics.
Caveat
This is a hypothesis from reading the code, not a profiled result — worth confirming with an on-device
profiler (Android Studio Profiler / Perfetto) against an account with a large backfilled history before
committing to a specific fix, in case there's a second contributor (e.g. IMAP round-trip latency on open,
which is a separate concern from prefetch/FetchPolicy).
Acceptance criteria
Root cause confirmed via profiling against a large (multi-thousand message) cache.
Mailbox list query is scoped in SQL to the viewed account/folder rather than filtered in Kotlin.
Supporting composite index added, with a schema migration + migration test per #63.
Verified improvement on a large cache, ideally with a before/after measurement.
## Problem
Reported: message loading is slow even on a current flagship device (Pixel 10 XL). The likely
architectural cause, from reading the mailbox list's data path:
- `MessageDao.observeSummaries()` (`MessageDao.kt:20-24`) has no `WHERE`/`LIMIT` — it observes *every*
cached message across *every* account and folder, and re-emits the full result set on **any** write to
the `messages` table (a new message arriving via IDLE, a star/read-flag toggle, a sync of an unrelated
folder, …).
- `MailboxViewModel.messages` (`MailboxViewModel.kt:96-111`) then runs a client-side `.filter{}` over that
entire cross-account/folder list on every recombination of its 4 input flows, just to get the handful
of rows for the currently-viewed account+folder. `LazyColumn` (`MailboxScreen.kt:270-299`) renders
whatever survives.
- There's no `Paging3`/`PagingSource` — the whole filtered list is held and diffed in memory.
- `MessageEntity` (`MessageEntity.kt:9-12`) indexes `accountId` and `timestampMillis` individually, but
not the `(accountId, folder, inInbox)` combination the list actually filters on.
This scales with **total cached message count**, not with what's visible or changed. Since the default
sync policy backfills a mailbox's entire history (#12, done) with keep-everything retention (#13, done),
a real, longstanding account's cache can grow to many thousands of rows — and every unrelated write
anywhere in `messages` re-triggers the full observe → filter → diff pipeline. `MessageSummary`'s own doc
comment (`MessageSummary.kt:8-11`) already records a prior incident in this exact path (a SQLite
CursorWindow overflow, issue #51) caused by not bounding what the list query pulls.
## Suggested approach
1. Push account/folder filtering into the SQL query (`WHERE accountId = :accountId AND folder = :folder`)
instead of `MailboxViewModel`'s in-memory `.filter{}`, so Room only re-queries/re-emits when a relevant
row actually changes.
2. Add a composite index covering `(accountId, folder, inInbox, timestampMillis)` to support that query
and the existing folder-scoped DAO methods.
3. Consider Room `PagingSource`/Paging3 for the `LazyColumn` so both query cost and recomposition cost
scale with what's on screen, not the whole cache. Search (`matchesSearch`, cross-field) can stay a
separate on-demand path since it already needs different semantics.
## Caveat
This is a hypothesis from reading the code, not a profiled result — worth confirming with an on-device
profiler (Android Studio Profiler / Perfetto) against an account with a large backfilled history before
committing to a specific fix, in case there's a second contributor (e.g. IMAP round-trip latency on open,
which is a separate concern from prefetch/`FetchPolicy`).
## Acceptance criteria
- [ ] Root cause confirmed via profiling against a large (multi-thousand message) cache.
- [ ] Mailbox list query is scoped in SQL to the viewed account/folder rather than filtered in Kotlin.
- [ ] Supporting composite index added, with a schema migration + migration test per #63.
- [ ] Verified improvement on a large cache, ideally with a before/after measurement.
Checked against the two open PRs touching this area: PR #45 (screen-lock) doesn't touch this code. PR #46 (fetch-all + retention, closes #12/#13) does touch MessageDao.kt/MailRepositoryImpl.kt/MessageEntity.kt, adding a materialized uid column and new backfill/retention queries — but it doesn't change observeSummaries(), add a composite (accountId, folder, inInbox, timestampMillis) index, or touch MailboxViewModel's client-side filtering. MailboxViewModel.kt isn't in PR #46's changed files at all.
If anything, PR #46 raises the priority here: it's precisely the change that removes the old 50-message-per-folder cap, so cached mailboxes will grow larger by default once it merges — making the unbounded/unfiltered list query more likely to bite. Still open after either PR merges.
Checked against the two open PRs touching this area: PR #45 (screen-lock) doesn't touch this code. PR #46 (fetch-all + retention, closes #12/#13) does touch `MessageDao.kt`/`MailRepositoryImpl.kt`/`MessageEntity.kt`, adding a materialized `uid` column and new backfill/retention queries — but it doesn't change `observeSummaries()`, add a composite `(accountId, folder, inInbox, timestampMillis)` index, or touch `MailboxViewModel`'s client-side filtering. `MailboxViewModel.kt` isn't in PR #46's changed files at all.
If anything, PR #46 raises the priority here: it's precisely the change that removes the old 50-message-per-folder cap, so cached mailboxes will grow larger by default once it merges — making the unbounded/unfiltered list query more likely to bite. Still open after either PR merges.
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
Problem
Reported: message loading is slow even on a current flagship device (Pixel 10 XL). The likely
architectural cause, from reading the mailbox list's data path:
MessageDao.observeSummaries()(MessageDao.kt:20-24) has noWHERE/LIMIT— it observes everycached message across every account and folder, and re-emits the full result set on any write to
the
messagestable (a new message arriving via IDLE, a star/read-flag toggle, a sync of an unrelatedfolder, …).
MailboxViewModel.messages(MailboxViewModel.kt:96-111) then runs a client-side.filter{}over thatentire cross-account/folder list on every recombination of its 4 input flows, just to get the handful
of rows for the currently-viewed account+folder.
LazyColumn(MailboxScreen.kt:270-299) renderswhatever survives.
Paging3/PagingSource— the whole filtered list is held and diffed in memory.MessageEntity(MessageEntity.kt:9-12) indexesaccountIdandtimestampMillisindividually, butnot the
(accountId, folder, inInbox)combination the list actually filters on.This scales with total cached message count, not with what's visible or changed. Since the default
sync policy backfills a mailbox's entire history (#12, done) with keep-everything retention (#13, done),
a real, longstanding account's cache can grow to many thousands of rows — and every unrelated write
anywhere in
messagesre-triggers the full observe → filter → diff pipeline.MessageSummary's own doccomment (
MessageSummary.kt:8-11) already records a prior incident in this exact path (a SQLiteCursorWindow overflow, issue #51) caused by not bounding what the list query pulls.
Suggested approach
WHERE accountId = :accountId AND folder = :folder)instead of
MailboxViewModel's in-memory.filter{}, so Room only re-queries/re-emits when a relevantrow actually changes.
(accountId, folder, inInbox, timestampMillis)to support that queryand the existing folder-scoped DAO methods.
PagingSource/Paging3 for theLazyColumnso both query cost and recomposition costscale with what's on screen, not the whole cache. Search (
matchesSearch, cross-field) can stay aseparate on-demand path since it already needs different semantics.
Caveat
This is a hypothesis from reading the code, not a profiled result — worth confirming with an on-device
profiler (Android Studio Profiler / Perfetto) against an account with a large backfilled history before
committing to a specific fix, in case there's a second contributor (e.g. IMAP round-trip latency on open,
which is a separate concern from prefetch/
FetchPolicy).Acceptance criteria
Checked against the two open PRs touching this area: PR #45 (screen-lock) doesn't touch this code. PR #46 (fetch-all + retention, closes #12/#13) does touch
MessageDao.kt/MailRepositoryImpl.kt/MessageEntity.kt, adding a materializeduidcolumn and new backfill/retention queries — but it doesn't changeobserveSummaries(), add a composite(accountId, folder, inInbox, timestampMillis)index, or touchMailboxViewModel's client-side filtering.MailboxViewModel.ktisn't in PR #46's changed files at all.If anything, PR #46 raises the priority here: it's precisely the change that removes the old 50-message-per-folder cap, so cached mailboxes will grow larger by default once it merges — making the unbounded/unfiltered list query more likely to bite. Still open after either PR merges.