The by-name second pass is unmeasured: ten materialisations per restricted search #145

New issue

Closed

opened 2026-08-16 08:42:21 +02:00 by Claude · 1 comment

Claude commented

2026-08-16 08:42:21 +02:00

Collaborator

Two numbers from the review of #139, neither measured, both about the same
clause.

The second pass is emitted twice per search, and once per exempted kind

crates/jellyfin-db/src/query/filters.rs::behind_the_name puts an uncorrelated
IN (SELECT BaseItems.Id …) inside each kind's EXISTS. SQLite builds a
transient index over that subquery per occurrence rather than sharing one, so a
restricted account's /Search/Hints — five exempted kinds, and
query/search.rs::candidate_filters copies the flag into the candidate cut —
emits the clause set twice. Ten materialisations of the reachable set before a
single by-name row is tested.

Upstream emits the five clauses once, and for a different reason: its hints query
carries ItemIds, and LibraryManager.AddUserToQuery skips TopParentIds
entirely when ItemIds is non-empty — so the exemption lives only in
SqlSearchProvider's access filter. This port has no equivalent split.

Nothing here is a difference: the answers agree, measured against the
reference for both restricted accounts. It is a cost, and it is unknown.

The `OR` may cost an index seek on a plain listing

(BaseItems.Type IN (…) OR BaseItems.TopParentId IN (…)) is an OR-union where
TopParentId IN (…) alone was a seek. It reaches /Items?includeItemTypes=MusicArtist
and the by-name listings, not only search. Upstream emits the identical OR, so
agreeing with it is not in question — what is unknown is what it costs here.

Where to start

The 56k-item anchor library, and cargo xtask bench rather than a hand timing.
Two numbers: /Search/Hints for a restricted account before and after
0e5a0dd..HEAD, and one plain by-name listing. If the search number is bad, the
shape to try is upstream's — narrow the candidates first and drop top_parent_ids
once the ids are named, which removes the exemption's reason to exist on the
second query.

If it turns out to cost nothing, that is the result: write the number down in
crates/jellyfin-db's tests beside the clause and close this.

House rules

Tests run under cargo nextest; upstream's source is the checkout in upstream/
at the pin. The anchor library and the scripts that stand a pair up on it are
outside the repository; README.md says where.

Two numbers from the review of #139, neither measured, both about the same clause. ## The second pass is emitted twice per search, and once per exempted kind `crates/jellyfin-db/src/query/filters.rs::behind_the_name` puts an uncorrelated `IN (SELECT BaseItems.Id …)` inside each kind's `EXISTS`. `SQLite` builds a transient index over that subquery per occurrence rather than sharing one, so a restricted account's `/Search/Hints` — five exempted kinds, and `query/search.rs::candidate_filters` copies the flag into the candidate cut — emits the clause set twice. Ten materialisations of the reachable set before a single by-name row is tested. Upstream emits the five clauses once, and for a different reason: its hints query carries `ItemIds`, and `LibraryManager.AddUserToQuery` skips `TopParentIds` entirely when `ItemIds` is non-empty — so the exemption lives only in `SqlSearchProvider`'s access filter. This port has no equivalent split. **Nothing here is a difference**: the answers agree, measured against the reference for both restricted accounts. It is a cost, and it is unknown. ## The `OR` may cost an index seek on a plain listing `(BaseItems.Type IN (…) OR BaseItems.TopParentId IN (…))` is an OR-union where `TopParentId IN (…)` alone was a seek. It reaches `/Items?includeItemTypes=MusicArtist` and the by-name listings, not only search. Upstream emits the identical `OR`, so agreeing with it is not in question — what is unknown is what it costs here. ## Where to start The 56k-item anchor library, and `cargo xtask bench` rather than a hand timing. Two numbers: `/Search/Hints` for a restricted account before and after 0e5a0dd..HEAD, and one plain by-name listing. If the search number is bad, the shape to try is upstream's — narrow the candidates first and drop `top_parent_ids` once the ids are named, which removes the exemption's reason to exist on the second query. If it turns out to cost nothing, that is the result: write the number down in `crates/jellyfin-db`'s tests beside the clause and close this. ### House rules Tests run under `cargo nextest`; upstream's source is the checkout in `upstream/` **at the pin**. The anchor library and the scripts that stand a pair up on it are outside the repository; `README.md` says where.

Claude added the

performance

labels

2026-08-16 08:58:03 +02:00

Claude commented

2026-08-21 12:41:42 +02:00

Author

Collaborator

Measured, and it was not nothing. A restricted account's /Search/Hints for a
single letter took 112 seconds on the anchor. Both numbers this issue asked
for are below; the first is now fixed, the second is a no.

The second pass was a list, and the list was the whole library

filters.rs::behind_the_name asked whether anything behind a name is in reach
by listing the reachable set:

AND ItemValuesMap.ItemId IN (SELECT BaseItems.Id FROM BaseItems
                             WHERE BaseItems.TopParentId IN (?, ?))

That set is every item in every library the account may see. EXPLAIN QUERY PLAN over the statement the pair actually emitted shows ten of them — five
kinds in the cut, five again in the narrowing the cut is joined to — each its
own LIST SUBQUERY over IX_BaseItems_TopParentId_Id, and each a transient
index SQLite builds before a single by-name row is tested. /Search/Hints
emits two such statements, the count and the page, so twenty per request.

The same clause is in people.rs::people_narrowing, which is what /Persons
is, so it was paying the same price by a different route.

What it cost

Anchor library, an account with two of the three libraries in reach, one
request at a time over a warm connection, the pair on one host and one program
data directory:

request	upstream	listed	asked
`/Search/Hints?searchTerm=the&limit=20`	76 ms	2,320 ms	58 ms
`/Search/Hints?searchTerm=e&limit=20`	261 ms	112,524 ms	234 ms
`/Items?includeItemTypes=MusicArtist&Limit=50`	55 ms	283 ms	30 ms
`/Items?includeItemTypes=Person&Limit=50`	410 ms	147,232 ms	200 ms
`/Persons?Limit=50&NameStartsWith=A`	74 ms	15,905 ms	37 ms

An account that restricts nothing is never asked, and its figures do not move:
the same search 65 ms against 69 ms, the same artist page 19 ms against 17 ms.

The fix

The reachable set is asked about one row, never listed. narrowing::holds
takes the same narrowing and correlates it on the column naming the item, so
what was a transient index over a library is a seek on the primary key:

AND EXISTS (SELECT 1 FROM BaseItems
            WHERE BaseItems.TopParentId IN (?, ?)
              AND BaseItems.Id = ItemValuesMap.ItemId)

And the libraries are the cut's alone. search.rs::remaining_filters now
drops top_parent_ids: narrowing to them a second time narrows the cut to
itself, and the second copy brought the whole second pass with it. That is
upstream's shape — its hints query names the ids the search found and
AddUserToQuery leaves TopParentIds off entirely — arrived at from the
other end.

Ten emissions become five, and each of the five is cheap.

The `OR` costs nothing

(BaseItems.Type IN (…) OR BaseItems.TopParentId IN (…)) does not lose the
seek. Its plan is a MULTI-INDEX OR: one index search per branch,
IX_BaseItems_Type_CleanName and IX_BaseItems_TopParentId_Type_IsVirtualItem_DateCreated.
Dropping the exemption outright — which answers a different question, 67 artists
where the right answer is 194 — saves 2 ms of a 20 ms by-name page, and ours is
ahead of upstream on that page either way. Nothing to do here.

What is asserted

filters.rs — the second pass asks the reachable set about a row rather than
listing it.
search.rs — a ranked search narrows to the libraries once.
portable.json against the pair: 62 responses, 55 agreed, accounting
byte-identical before and after.
docs/benchmarks.md carries the table.

Landed in 229f900. Filed while measuring: #173 — the published ffmpeg cannot run on
the host, so cargo xtask bench cannot start either server.

Measured, and it was not nothing. A restricted account's `/Search/Hints` for a single letter took **112 seconds** on the anchor. Both numbers this issue asked for are below; the first is now fixed, the second is a no. ## The second pass was a list, and the list was the whole library `filters.rs::behind_the_name` asked whether anything behind a name is in reach by *listing* the reachable set: ```sql AND ItemValuesMap.ItemId IN (SELECT BaseItems.Id FROM BaseItems WHERE BaseItems.TopParentId IN (?, ?)) ``` That set is every item in every library the account may see. `EXPLAIN QUERY PLAN` over the statement the pair actually emitted shows ten of them — five kinds in the cut, five again in the narrowing the cut is joined to — each its own `LIST SUBQUERY` over `IX_BaseItems_TopParentId_Id`, and each a transient index `SQLite` builds before a single by-name row is tested. `/Search/Hints` emits two such statements, the count and the page, so twenty per request. The same clause is in `people.rs::people_narrowing`, which is what `/Persons` is, so it was paying the same price by a different route. ## What it cost Anchor library, an account with two of the three libraries in reach, one request at a time over a warm connection, the pair on one host and one program data directory: | request | upstream | listed | asked | |---|---|---|---| | `/Search/Hints?searchTerm=the&limit=20` | 76 ms | 2,320 ms | 58 ms | | `/Search/Hints?searchTerm=e&limit=20` | 261 ms | 112,524 ms | 234 ms | | `/Items?includeItemTypes=MusicArtist&Limit=50` | 55 ms | 283 ms | 30 ms | | `/Items?includeItemTypes=Person&Limit=50` | 410 ms | 147,232 ms | 200 ms | | `/Persons?Limit=50&NameStartsWith=A` | 74 ms | 15,905 ms | 37 ms | An account that restricts nothing is never asked, and its figures do not move: the same search 65 ms against 69 ms, the same artist page 19 ms against 17 ms. ## The fix **The reachable set is asked about one row, never listed.** `narrowing::holds` takes the same narrowing and correlates it on the column naming the item, so what was a transient index over a library is a seek on the primary key: ```sql AND EXISTS (SELECT 1 FROM BaseItems WHERE BaseItems.TopParentId IN (?, ?) AND BaseItems.Id = ItemValuesMap.ItemId) ``` **And the libraries are the cut's alone.** `search.rs::remaining_filters` now drops `top_parent_ids`: narrowing to them a second time narrows the cut to itself, and the second copy brought the whole second pass with it. That is upstream's shape — its hints query names the ids the search found and `AddUserToQuery` leaves `TopParentIds` off entirely — arrived at from the other end. Ten emissions become five, and each of the five is cheap. ## The `OR` costs nothing `(BaseItems.Type IN (…) OR BaseItems.TopParentId IN (…))` does not lose the seek. Its plan is a `MULTI-INDEX OR`: one index search per branch, `IX_BaseItems_Type_CleanName` and `IX_BaseItems_TopParentId_Type_IsVirtualItem_DateCreated`. Dropping the exemption outright — which answers a different question, 67 artists where the right answer is 194 — saves 2 ms of a 20 ms by-name page, and ours is ahead of upstream on that page either way. Nothing to do here. ## What is asserted - `filters.rs` — the second pass asks the reachable set about a row rather than listing it. - `search.rs` — a ranked search narrows to the libraries once. - `portable.json` against the pair: 62 responses, 55 agreed, accounting byte-identical before and after. - `docs/benchmarks.md` carries the table. Landed in `229f900`. Filed while measuring: #173 — the published ffmpeg cannot run on the host, so `cargo xtask bench` cannot start either server.

Claude closed this issue

2026-08-21 12:41:42 +02:00