Studios page extremely slow on large image libraries

Have you enabled troubleshooting mode?

• I confirm that the troubleshooting mode is enabled.

Describe the bug

My understanding of the issue is limited and I used Claude to troubleshoot. My understanding is that when loading the Studios page it does an SQL query for images. Part of the query to load the studio cards involves a join with the images table which ends up querying and sorting the entire images table for each card.

Below is a more technically in-depth description of the issue that Claude wrote about what we (it) discovered during our troubleshooting:

The Studios page takes tens of seconds to load. Performers, Tags, Scenes and Galleries pages are all fast. The problem scales with the number of studio cards shown on the page.

The cause is the per-studio performer count. Each studio card fires this query:

WITH studio(root_id, item_id) AS (VALUES(1150, 1150)),
performer_studio AS (
  SELECT performer_id FROM scenes
    INNER JOIN performers_scenes ON scenes.id = performers_scenes.scene_id
    INNER JOIN studio ON scenes.studio_id = studio.item_id
  UNION SELECT performer_id FROM images
    INNER JOIN performers_images ON images.id = performers_images.image_id
    INNER JOIN studio ON images.studio_id = studio.item_id
  UNION SELECT performer_id FROM galleries
    INNER JOIN performers_galleries ON galleries.id = performers_galleries.gallery_id
    INNER JOIN studio ON galleries.studio_id = studio.item_id)
SELECT COUNT(*) as count FROM (SELECT DISTINCT performers.id FROM performers
  LEFT JOIN performer_studio ON performers.id = performer_studio.performer_id
  WHERE (performer_studio.performer_id IS NOT NULL)) as temp;

With ~25 studios on the page this runs ~25 times, each logged at 8.3–9.3 seconds:

SLOW SQL [9.096956104s]: WITH studio(root_id, item_id) AS (VALUES(1150, 1150)), performer_studio AS (...
SLOW SQL [9.089636566s]: WITH studio(root_id, item_id) AS (VALUES(1919, 1919)), performer_studio AS (...
SLOW SQL [8.775262595s]: WITH studio(root_id, item_id) AS (VALUES(737, 737)),   performer_studio AS (...

Unrelated queries on the page are then starved by the contention. This trivial lookup on 25 IDs took nearly 27 seconds purely from waiting:

SLOW SQL [26.917519841s]: SELECT `studio_custom_fields`.`studio_id` AS `id`, `field`, `value`
FROM `studio_custom_fields` WHERE (`studio_custom_fields`.`studio_id` IN (1301, 1784, ...))

Root cause

EXPLAIN QUERY PLAN on the query above:

QUERY PLAN
|--CO-ROUTINE temp
|  |--CO-ROUTINE performer_studio
|  |  `--COMPOUND QUERY
|  |     |--LEFT-MOST SUBQUERY
|  |     |  |--MATERIALIZE studio
|  |     |  |  `--SCAN CONSTANT ROW
|  |     |  |--SCAN studio
|  |     |  |--SEARCH scenes USING COVERING INDEX index_scenes_on_studio_id (studio_id=?)
|  |     |  `--SEARCH performers_scenes USING COVERING INDEX sqlite_autoindex_performers_scenes_1 (scene_id=? AND performer_id>?)
|  |     |--UNION USING TEMP B-TREE
|  |     |  |--SCAN studio
|  |     |  |--BLOOM FILTER ON images (studio_id=?)
|  |     |  |--SEARCH images USING AUTOMATIC COVERING INDEX (studio_id=?)
|  |     |  `--SEARCH performers_images USING COVERING INDEX sqlite_autoindex_performers_images_1 (image_id=? AND performer_id>?)
|  |     `--UNION USING TEMP B-TREE
|  |        |--SCAN studio
|  |        |--SEARCH galleries USING COVERING INDEX index_galleries_on_studio_id (studio_id=?)
|  |        `--SEARCH performers_galleries USING COVERING INDEX sqlite_autoindex_performers_galleries_1 (gallery_id=?)
|  |--SCAN performers
|  |--BLOOM FILTER ON performer_studio (performer_id=?)
|  `--SEARCH performer_studio USING AUTOMATIC PARTIAL COVERING INDEX (performer_id=?)
`--SCAN temp

Every branch uses a proper index except one:

|--SEARCH images USING AUTOMATIC COVERING INDEX (studio_id=?)

AUTOMATIC means SQLite found no usable index and built a throwaway one at query time — a full scan and sort of the entire images table, discarded when the query ends and rebuilt from scratch for the next studio card.

The reason it is not usable is that the index is partial, while the equivalent indexes on scenes and galleries are not:

-- images (partial)
CREATE INDEX `index_images_on_studio_id` ON `images` (`studio_id`) WHERE `studio_id` IS NOT NULL;

-- scenes / galleries (unconditional)
CREATE INDEX index_scenes_on_studio_id    ON scenes (studio_id);
CREATE INDEX index_galleries_on_studio_id ON galleries (studio_id);

SQLite will only use a partial index when it can prove from the query text that every row it needs satisfies the index's WHERE condition. Here the constraint arrives through a join (INNER JOIN studio ON images.studio_id = studio.item_id), and the planner does not infer studio_id IS NOT NULL across a join. It therefore rejects the index.

The same partial index is used when the constraint is written directly against the column in the same query block, which is why the other count queries on the very same page are microsecond-fast on the very same table:

SQL [308.246µs]: SELECT COUNT(*) as count FROM (SELECT DISTINCT images.id FROM images
WHERE (images.studio_id IN (SELECT column2 FROM (VALUES(1555, 1555))))) as temp

Same table, same column, same studio. IN → 308µs. INNER JOIN → seconds.

Timings

Query above, studio ID 1150, run directly against the database file with the sqlite3 CLI:

The warm figure is almost entirely user CPU, so ~4.2s is the irreducible cost of rebuilding the automatic index. It is not an I/O or hardware problem, and no amount of RAM or faster storage removes it. Multiplied by ~25 studio cards, this is the page load time.

ANALYZE does not help — this is not a stale-statistics issue, the index is genuinely unusable for this query shape.

Steps to reproduce

1. Have a library with a large images table (5.6M rows here).
2. Open the Studios page.
3. Observe the page taking tens of seconds, and SLOW SQL entries for the performer-count query, one per studio card, in the logs at Trace/Debug level.

Expected behaviour

The performer count should use index_images_on_studio_id, as the scene and gallery counts already use their equivalents, and complete in milliseconds.

Screenshots or additional context

Workaround

Adding an unconditional index on images.studio_id gives the planner something it can use across the join. With Stash stopped, and after taking a backup:

sql CREATE INDEX custom_images_studio_id_full ON images(studio_id);

The AUTOMATIC COVERING INDEX line in the query plan is then replaced by a normal index search.

Cost is a few minutes to build and roughly 60–100MB, since it also indexes rows where studio_id is NULL. The original partial index is left in place so schema migrations are unaffected, and the name is deliberately prefixed custom_ to avoid colliding with any index Stash may add later.

Suggested fix

Either would resolve it:

Drop the WHERE studio_id IS NOT NULL clause from index_images_on_studio_id, making it consistent with the scenes and galleries indexes. The partial clause saves relatively little space and costs the planner the ability to use the index in any join-shaped query. Rewrite the images branch of the CTE to constrain the column directly, matching the form already used by the fast count queries elsewhere on the same page: sql UNION SELECT performer_id FROM images INNER JOIN performers_images ON images.id = performers_images.image_id WHERE images.studio_id IN (SELECT item_id FROM studio)

Option 1 is the smaller change and also protects any future query that joins on images.studio_id. Option 2 avoids a migration.

Worth checking whether other tables carry the same partial-index pattern, since the same trap applies anywhere the column is constrained through a join rather than directly.

Stash version

v0.31.1

Device details

OS / platform: Docker Browsers: Firefox (Fedora), Safari (iOS/macOS) Database location / storage: iSCSI to RAID 10 of SSD's SQLite version: 3.46.1 Library size: ~5,600,000 images, 13,389 performers, ~20 studios shown per page

Relevant log output