Slow `lists.stats` Performance with Tag Searches in Smart Lists on Large Databases

Summary

In Karakeep 0.33.2, lists.stats becomes significantly slow when there are numerous Smart Lists containing tag-based conditions.

On my test environment, modifying the positive tagName search from a bookmarks-first correlated EXISTS query to a tag-first search order improved lists.stats performance from approximately 16.3 seconds → 2.3 seconds.

Environment

• Karakeep version: 0.33.2
• SQLite version: 3.46.1
• Hardware: Raspberry Pi
• Bookmarks count: 42,017
• Tags on bookmarks: 348,434
• Number of Smart Lists: 40
• Write-Ahead Logging (WAL): enabled
• PRAGMA optimize command executed

Reproduction Results

Stock 0.33.2:

16.100 seconds
16.264 seconds
16.407 seconds
16.341 seconds

Warm average: ~16.28 seconds

This time duration applies solely to lists.stats.

In contrast, bookmarks.getBookmarks completed in approximately 0.26 seconds under the same conditions.

Investigation Findings

For queries similar to our current positive tag search implementation, SQLite executes a correlated subquery starting from the bookmarks table.

SEARCH b ... (userId=?)
CORRELATED SCALAR SUBQUERY
SEARCH bt ... (userId=? AND name=?)
SEARCH tob ... (tagId=? AND bookmarkId=?)

A single-tag search using #UI yielded an average execution time of approximately:

0.10255 seconds

However, when enforcing the following query execution order:

bookmarkTags
→ tagsOnBookmarks
→ bookmarks

the query plan becomes:

SEARCH bt ... (userId=? AND name=?)
SEARCH tob ... (tagId=?)
SEARCH b ... (id=?)

resulting in the same search operation completing in an average of approximately:

0.000031 seconds

Experimental Patch

We modified only the tag-first ordering for positive tagName values, leaving negative tag searches unchanged.

Results showed:

2.285 seconds
2.330 seconds
2.310 seconds
2.338 seconds
2.256 seconds

Warm average: 2.309 seconds

This resulted in lists.stats being approximately 7 times faster.

SQLite's statistics indicate:

tagsOnBookmarks_tagId_bookmarkId_idx: 348434 6 1

showing that searches starting from tagId have high selectivity.

If required, we can submit this minimal modification as a pull request.

I'm new to opening issues on GitHub, so if anything here is inappropriate, incorrect, or doesn't follow the project's conventions, please feel free to point it out.