Plexamp's Library overview screen extremely slow

[triks_melb]

Server Version#: 1.43.3.10828-00f62d37d
Player Version#: Plexamp for iOS 4.12.14 (iPhone 17 Pro Max / 254GB Available Space)

iPhone Wi-Fi iPerf Test to Plex Server:
Download: 902 Mbits/s Average
Upload: 858 Mbits/s Average

---

Opening Plexamp’s Library overview screen (Albums/Tracks/{main culprits} category list with item counts) takes 1-2 minutes to fully load. This is reproducible and confirmed independent of hardware — happens identically regardless of RAM allocation (tested at 4GB/8GB/12GB), disk type, or database journal mode (WAL vs delete).

Evidence — Plex’s own SLOW QUERY log warnings:

Warning - [Req#644] SLOW QUERY: It took 53810.000000 ms to retrieve 0 items.
Warning - [Req#19f] SLOW QUERY: It took 71050.000000 ms to retrieve 0 items.
Warning - [Req#642] SLOW QUERY: It took 14160.000000 ms to retrieve 21 items.
Debug - [Req#643] It took 20770.000000 ms to retrieve 157 items.
Debug - [Req#645] It took 24120.000000 ms to retrieve 835 items.
Debug - [Req#641] It took 61790.000000 ms to retrieve 303 items.

These map directly to the category counts on the Library screen (e.g. the 157-item query is the Album Genres count, 303 is Moods, etc.). Full breakdown of every category/endpoint/timing can be provided for further troubleshooting.

Root cause (confirmed via direct query capture)

I attached gdb to the running Plex Media Server process with a breakpoint on sqlite3_prepare_v2 to capture the actual SQL being executed. This is the real query behind the per-letter album count (and likely the same code path used for the overview counts):

select upper(substr(parents.title_sort, 1, 1)) as c, count(distinct metadata_items.id)
from metadata_items
join metadata_items as parents on metadata_items.parent_id = parents.id
join metadata_items as children on children.parent_id = metadata_items.id
where metadata_items.library_section_id = 1 and metadata_items.metadata_type = 9
group by c

The children self-join is never referenced in the SELECT list — seems like its only purpose is verifying each album has at least one track (a “has children” existence check). Implemented as a full JOIN rather than EXISTS, this multiplies every album row by its track count before aggregation. With ~8.8 tracks/album average, that turns ~165K rows of real work into on the order of ~1.4 million intermediate join rows just to compute a per-letter count.

Possible fix?

Would replacing join metadata_items as children on children.parent_id = metadata_items.id with WHERE EXISTS (SELECT 1 FROM metadata_items AS children WHERE children.parent_id = metadata_items.id) (or equivalent semi-join) in the music library aggregate/count query paths preserve identical filtering semantics without the row multiplication?

What I ruled out before making the conclusion it’s most likely a query bug and not environmental or config:

1. Repeating the identical slow request back-to-back: no speedup (rules out cold cache)
2. Raw SELECT COUNT(*) against the same tables: 12-63ms (rules out missing indexes/corruption)
3. /playlists/all (structurally similar “list everything” endpoint, smaller table): 50ms on the same server
4. RAM increasing 4GB to 12GB: no change
5. journal_mode switched from delete to WAL: no change to this specific symptom
6. Database verified clean via ChuckPa’s DBRepair (check/repair/reindex/FTS rebuild, all passed)
7. Tested adding a covering index on the tags/taggings join tables which improved Genre/Style/Mood by 2-3x but regressed Albums/Tracks (confirmed live), consistent with the planner reacting to the underlying JOIN-fanout problem rather than a missing index
8. Hypervisor/storage benchmarked directly: NVMe SSD sequential read/write ~2.7/2.5 GB/s, ~19K random 4K IOPS; RAM bandwidth ~8-13 GB/s — nowhere near a bottleneck for any SQLite query.

Why its unlikely due to just size of library:

1. A raw SELECT COUNT(*) against the same 1.44M tracks / 165K albums completes in 12-63 milliseconds. Any database counts that many rows near-instantly — this isn’t a large table by modern standards. If size alone were the issue, this would be slow too. It isn’t.
2. /playlists/all is fast (50ms) on the same server, same library, same hardware. If the library were too big for Plex to handle, everything touching a large dataset would be uniformly slow. It’s specifically the queries containing the unnecessary children self-join that are slow — a query-shape problem, not a data/volume problem.
3. The captured query doesn’t need to touch 1.4 million rows to count 165K albums by first letter — it only does so because of the unnecessary JOIN. Replace it with EXISTS and the same “large” library would be counted in milliseconds, because EXISTS short-circuits on the first match instead of materializing every album × every track combination. The library size didn’t create this cost its the query shape that does.
4. 71 seconds to return zero rows isn’t what a query scaling proportionally with library size looks like — it’s what a query does when it performs work proportional to the wrong thing (total track count) instead of the thing actually being asked for (26 letter buckets).

Can provide full debug logs or the complete timing table if useful to you guys.

---

Hypervisor:

• Proxmox VE 9.2.4 (kernel 7.0.14-5-pve)
• CPU: Intel Core i7-12700H (14 cores / 20 threads, 1 socket)
• RAM: 31GB total
• Storage backing the container: NVMe SSD (TEAM TM8FPK002T, 2TB)

LXC Container (Plex Music Server):

• Type: unprivileged LXC container, Debian
• Allocated: 8 CPU cores, 12GB RAM (raised from 4GB during this troubleshooting — no effect on the issue)
• Root disk: 500GB, local-lvm (LVM-thin on the NVMe SSD above)
• Music library storage: NFS mount (/mnt/nas/music), separate from the OS/database disk — the Plex database itself lives on local NVMe SSD, not network storage
• Network: bridged (vmbr0), static IP

[elan]

That is cool analysis, and I’ll just start by saying your library is waaaaay over what I would consider “supported” size

However, that doesn’t mean we shouldn’t optimize where we can!

The gdb breakpoint on sqlite3_prepare_v2 is genuinely good work, and the query you caught does have the problem you describe. I’ve measured it. But it isn’t what’s making your library screen slow, and the step that connects the two doesn’t hold up.

The log lines aren’t that query.

SLOW QUERY: It took X ms to retrieve N items is emitted from exactly one place in the server: a helper that materializes a list of objects: albums, tags, tracks. The per-letter count query you captured doesn’t go through it. It reads raw rows and never builds objects, so it can’t produce that log line. Every timing you pasted is from some other query.

The gdb capture and the log lines are two separate findings that were never actually connected. “Likely the same code path” is carrying the entire argument, and it’s false.

That also explains your index result, which you flagged as mysterious. A covering index on tags/taggings helping Genre/Style/Mood 2-3× while regressing Albums/Tracks isn’t the planner reacting to join fanout, it’s straightforward evidence that those are different queries with different plans. One got faster, the other got slower. Nothing shared.

I measured your proposed fix. It’s real, and it’s about 2×.

On a 6,300-album / 76,000-track music library, warm cache, five runs:

• per-letter count as shipped: 48 ms
• children join rewritten as a semi-join: 23 ms
• output byte-identical

So: correct diagnosis of the query shape, correct fix, 2.1×, worth taking. But scale it to your 165K albums and you get roughly 1.2 seconds, not 71. The reason the fanout is cheap is that parent_id is indexed, so SQLite resolves that join as a covering-index probe rather than anything quadratic. You’re right that it does unnecessary work. You’re wrong by about fifty-fold about how much.

The category counts are a different query, and it has its own version of your bug.

Genres/Moods/Styles come from the tag-counting path. Same methodology:

• as shipped: 823 ms (252K taggings)
• one unused join removed: 735 ms
• two unused joins removed: 451 ms — identical results

45% of that query is joins whose columns are never selected and never filtered on. Same class of defect you identified, in the query that’s actually in your slow path rather than the one gdb happened to catch. At your scale that extrapolates to roughly 15 seconds warm, which is the right order of magnitude for 61 seconds on a cold LXC.

What nobody has established yet, me included:

15 seconds extrapolated against 61 seconds observed is a plausibility argument, not a diagnosis. And your two worst lines (53 s and 71 s returning zero rows) are still unexplained by anything in this thread. A query that does a minute of work and returns nothing is the signature of either a threshold filter applied after the work is done, or a query plan that collapsed. Those have very different fixes.

Two experiments would settle it, and neither needs gdb:

1. EXPLAIN QUERY PLAN on the slow queries. If anything reports SCAN where you’d expect SEARCH, that’s your minute, and it will dwarf every fanout discussed here. This is the cheapest high-information thing available and it’s conspicuously the one measurement you didn’t take.
2. ANALYZE; on the library DB, then re-test. Your covering index gave you 2-3×. I built the identical index on my copy and got zero change — 823 ms before, 831 ms after, with the planner adopting it. That divergence points at stale sqlite_stat1 statistics, which would mean the planner is making bad choices on your machine for reasons that have nothing to do with query text. If that’s it, no amount of rewriting JOIN as EXISTS will help you.

Also useful: the raw request URLs behind those six Req# entries, so the timings can be matched to endpoints rather than inferred from item counts.

Short version: you found two real inefficiencies, both worth fixing, both worth about 2×. Your missing minute is still missing, and my money is on the query plan rather than the query shape.

[triks_melb]

Hi Elan,

Really appreciate you taking the time to actually measure this stuff instead of just waving it off and you were right on every point. The gdb/log connection was an estimation and calling out EXPLAIN QUERY PLAN. I ran both experiments properly tonight: attached to the live process and pulled the actual SQL text mid-execution instead of guessing, so these are the real queries, not presumptions.

EXPLAIN QUERY PLAN, Folder endpoint (/library/sections/1/folder?parent=-1)

Every join is a SEARCH on a proper index-directories, metadata_items, media_items, media_parts, the parent/grandparent self-joins, metadata_item_settings. Zero scans. The last line is the actual cost:

USE TEMP B-TREE FOR ORDER BY

The ORDER BY is six columns deep (absolute_index, index, title_sort COLLATE icu_root, id, media_items.width DESC, originally_available_at) across two tables. icu_root is a custom collation you register at runtime — and there’s already a dedicated index_title_sort_icu index for it in the schema. But since no single index covers the entire six-column sort as a prefix, SQLite can’t use any of them for ordering and materializes the whole joined result into a temp B-tree, sorting it with a collation-function call per comparison. Every join is fast; the sort of the joined set isn’t. That’s a real, different finding from either of our original guesses.

EXPLAIN QUERY PLAN, Genre endpoint - clean. Every join is SEARCH on a proper index. The plan ends with SCAN (subquery-1), but that’s just the outer select * from (...) consuming the already-computed subquery output - not a real table scan. No forced sort, nothing to fix.

ANALYZE vs. the covering index - I need to walk this one back. Isolated it properly this time (baseline / index-only / index+ANALYZE / ANALYZE-only, 5 runs each, on the actual tag-counting query): medians for all four states land between 0.35s and 0.43s, and individual runs across all of them ranged from 0.29s to 0.48s - meaning the spread within any single state is as big as the spread between states. There’s no reproducible effect from either the index or ANALYZE. My original 2-3x number was almost certainly an artifact of comparing runs taken under different system load, not a real effect of the change I made — so thanks for pushing on that, because you were right to be suspicious of it. The stale-stats theory isn’t it either, since there’s no gap left to explain.

The 53s/71s zero-row queries - still not nailed down, and I want to be straight about that rather than paper over it. I tried to reproduce the exact conditions twice: a plain Plex restart, and then a full re-run of DBRepair (check/repair/reindex/FTS) followed by hitting the same endpoints on first touch, before anything could warm up. Neither came close — everything came back in 1-7 seconds with normal row counts both times.

One experience I can share from tonight was when separately, firing 9 simultaneous requests (roughly what Plexamp does on an initial browse - Albums, Tracks, Genre, Mood, Style, Folder all at once) drove one request to 67s and another past an 85s timeout. In a later, differently-shaped concurrency test I also managed to OOM-kill the Plex process outright. I can’t be sure those two things happened on the same run or are the same underlying event but between them, I think concurrent load is a strong candidate for what actually produced the original slow-query log lines, rather than a cold single-query execution. That would also explain why an isolated re-test can’t reproduce them. I don’t have a captured EXPLAIN QUERY PLAN on an actual zero-row instance, so call this a strong lead but not a diagnosis.

I believe there is one real and fixable inefficiency: (the Folder ORDER BY), your unnecessary-joins finding on the tag-counting query still holds since nothing here contradicts it, and one claim of my own that I retract. The concurrency theory for the big outliers is the only piece still open but I can’t fully close it out without catching one live.

Either way, this is a much better outcome to where I started, and that’s down to you actually checking my work instead of just taking it at face value so thanks for that.