Skip to content

Study management — read performance at real project sizes

PDF filter and Searches PDF counts (#4771)

Issue #4771 follows up #4719's open question 6 (PR #4764). The Studies workspace's PDF filter (POST api/projects/{projectId}/studies with PdfStatusFilter) and the Searches pane's GET …/studies/pdf-status/search-counts classify studies in C#. Design decision 1 in the implementation plan's

4719 section explains why. Measured on 5 November 2026 against main @ bbddca15df.

Where it was measured: local, not the preview

These figures come from a local single-node replica set (Docker mongo:8.0) on a shared 48-core host. They don't come from the PR preview, for two reasons:

  • the preview database syrf_pr_4831 can only be seeded with a write credential for the Preview Atlas cluster, and none was available to the measuring session;
  • the API on a preview signs in through Auth0 only. The E2E sign-in (/api/e2e-auth/login) answers only in the Development, Local and E2ETest environments (E2EAuthEnabledGate), so an authenticated call needs a real user session.

Compared with the preview, local figures are optimistic for two reasons. The whole collection fits in mongod's cache, and there's no network round trip between the API and Atlas. The ratios to the unfiltered list are the figures to compare. Host load varied between runs (load average 10–55 on 48 cores), which moves absolute times by about 30%.

Method

The harness is StudyPdfFilterScaleTimingTests in src/services/api/SyRF.API.Endpoint.Tests/PdfStatus/. It uses disposable synthetic data only and is opt-in:

  • it's skipped unless SYRF_PDF_SCALE_SIZES is set, and starts its replica-set container only then;
  • it refuses to run outside a Release build.
SYRF_PDF_SCALE_SIZES=50000,130000,215000 SYRF_PDF_SCALE_RUNS=5 \
  dotnet test src/services/api/SyRF.API.Endpoint.Tests -c Release \
  --filter "FullyQualifiedName~StudyPdfFilterScaleTimingTests" --logger "console;verbosity=detailed"
  • Code under test. The real StudyController, StudyRepository, StudyPdfAvailabilityRepository, StudyPdfAvailabilityAdmission and StudyPdfStatusClassifier, with both flags deciding on.
  • Indexes. pmStudy's startup indexes (InitialiseIndexesAsync) and the 0034 availability indexes.
  • Project shape. One project of N studies in 58 searches. The largest real project has about 215,000 studies in 58 searches. Another project of N/10 studies shares both collections, so every read has to stay scoped.
  • PDF state mix, per 100 studies:
Studies State
55 No PDF
20 Available
5 Unavailable
10 Not yet confirmed (no document)
2 Not yet confirmed (an older CheckedLinkKey)
1 Checking (a fresh lease)
1 Hosted elsewhere
3 Available (bulk-delivered, proven base)
1 Couldn't check
2 Deletion-marked, with their Available document left behind
  • Inputs. Link keys come from the real BulkPdfLinkService, with a legacy base and a proven bulk base.
  • Document size. Each study is about 1 KB, with a title and no abstract. One run used 3 KB abstracts (about 4 KB per document) to test sensitivity to document size.
  • Reads. Each list read asks for page 5 of 25, sorted by title, as the workspace does. Each read gets one warm-up, then five timed runs, and the table gives the median. The test asserts that the options partition the live studies as decision 1 says.
  • Database work. On one more untimed run, the MongoDB profiler (level 2) sums docs and keys examined for each served read.

Results

Median wall time in ms for the whole controller action. The ×-figure is the ratio to the unfiltered list of the same run.

Read 50k 130k 215k (run A) 215k (run B) 215k, 4 KB docs
Unfiltered list (today) 159 459 754 651 658
Without a confirmed PDF 755 (4.8×) 1,824 (4.0×) 2,911 (3.9×) 2,479 (3.8×) 2,653 (4.0×)
Available 468 (2.9×) 1,217 (2.7×) 2,924 (3.9×) 1,753 (2.7×) 1,767 (2.7×)
Not yet confirmed 672 (4.2×) 1,754 (3.8×) 3,159 (4.2×) 2,657 (4.1×) 2,599 (4.0×)
Unavailable 167 (1.1×) 393 (0.9×) 681 (0.9×) 592 (0.9×) 587 (0.9×)
No PDF 680 (4.3×) 2,056 (4.5×) 3,511 (4.7×) 2,820 (4.3×) 2,824 (4.3×)
Search counts 612 (3.8×) 1,515 (3.3×) 2,586 (3.4×) 2,132 (3.3×) 2,158 (3.3×)
  • Run A covers 50k, 130k and 215k in one process (host load about 24–55, rising through the run).
  • Run B is a repeat at 215k (load about 10–30).
  • The 4 KB run is otherwise the same as run B.

Every read but Unavailable grows linearly with the project and stays at 2.7–4.8 times the unfiltered list. At the largest project, a filtered page takes 1.75–3.5 s and a counts call 2.1–2.6 s, against 0.65–0.75 s for the unfiltered list.

Database work at 215k, from the profiler (run B):

Read Docs examined Keys examined Rows returned DB ms (sum)
Unfiltered list 210,700 632,100 27 715
Without a confirmed PDF 483,750 786,330 111,827 1,978
Available 161,275 452,378 111,851 1,123
Not yet confirmed 313,906 548,828 285,982 954
Unavailable 32,275 263,414 21,551 441
No PDF 328,950 632,101 210,726 1,510
Search counts 268,750 268,750 268,750 513

No read collection-scans. pmStudy is read through the {ProjectId} and {ProjectId, _id} indexes and the agreement COUNT_SCAN, and the availability collection through ProjectId_1_Status_1. The unfiltered list examines every study too, because the title sort fetches the whole project.

Where the time goes

Each part was timed alone at 215k (run B, median ms):

Part ms
Link inputs of every study (FilterPdfLinkInputsAsync, 210,700 rows) 1,288
Availability documents, Available only (58,050) 306
Availability documents, all (75,250) 446
Classification in memory, every study (link resolution, SHA-256 link key, rules) 287
Page by title with _id $in the 58,050 Available ids 439
Count with _id $nin the 58,050 Available ids 526
  1. Reading every study's link inputs is the largest cost. It's in No PDF, Not yet confirmed and the counts. About a third is mongod (420–460 ms to fetch 210,700 documents); the rest is moving and deserialising 210,700 rows in the API.
  2. Large id restrictions. The Available-first lever helps only when the candidate set is small. Unavailable (5%) is as fast as the unfiltered list. Available and Without a confirmed PDF carry 49,000–58,000 ids, twice: once to read the candidates and once to page and count. The restriction's Deletion == null term isn't in any index, so the restricted count fetches every matching study instead of counting keys.
  3. Classification in C# (about 290 ms) and the availability read (300–450 ms) are smaller but still linear.
  4. Document size made no difference locally (4 KB against 1 KB), because everything was in cache. On Atlas, where pmStudy (about 10 GB) doesn't fit in memory, the full-project fetches (cause 1, and the title sort that the unfiltered list shares) will cost more.

Database-side alternatives timed

These ran over a freshly seeded 215k project with the same harness, at load 17–23. Wall-time medians. "Today" is run B, except the counts, which were timed again in the same run. To reproduce them, add SYRF_PDF_SCALE_ALT=1. The harness then times each alternative over the project it has just measured and checks its answer against the expected counts. C writes a field to every synthetic study, so it runs last.

SYRF_PDF_SCALE_SIZES=215000 SYRF_PDF_SCALE_ALT=1 SYRF_PDF_SCALE_RUNS=5 \
  dotnet test src/services/api/SyRF.API.Endpoint.Tests -c Release \
  --filter "FullyQualifiedName~StudyPdfFilterScaleTimingTests" --logger "console;verbosity=detailed"

These are candidate reads written in the harness. They aren't the application's reads.

Alternative Read ms Today
A. The lever extended to the counts. Per-search totals by a $group on ProjectId, read from the {ProjectId, SystematicSearchId} index keys, minus the confirmed studies. The confirmed studies are found from the Available documents, and only those studies' link inputs are read. Search counts 1,343 2,180
D. A covering index {ProjectId, SystematicSearchId, PdfRelativePath, BulkPdfDeliveredPath, _id}. Today's read projects Deletion as well, so it isn't covered. Link inputs of every study, covered, 0 docs examined 780 1,263
Today's link-input read with the index present (not covered) 1,263 1,288
C. A stored classification, as a lower bound. A PdfClass string on pmStudy, written once from the C# rules, with indexes {ProjectId, PdfClass} and {ProjectId, SystematicSearchId, PdfClass}. The page sorts by title and counts, as today. Available 84 1,753
Without a confirmed PDF 275 2,479
No PDF 192 2,820
Not yet confirmed 55 2,657
Search counts (covered $group, 0 docs examined) 57 2,132

Reading the alternatives:

  • A cuts the counts by about 40%. They are still about twice the unfiltered list, still linear in the number of confirmed studies, and the list options are unchanged.
  • D saves about 0.5 s on the link-input read, but only if the read drops Deletion, and it leaves the in-API deserialisation and classification.
  • C puts every option and the counts below today's unfiltered list.

Decision

The owner decided on 5 November 2026, recorded in the owner's #4771 decision:

  • Store the PDF classification (alternative C) on each study: its link kind and current check result, with a configuration fingerprint. Extending the lever to the counts (alternative A) is not the fix.
  • While a check is in progress, correct the page and counts from the in-progress checks, rather than accept the 2-minute lag.

The follow-up slice is tracked in #4771. studiesWorkspace and studyPdfStatus don't go on anywhere that has projects of this size until it ships.

Why:

  • Today the reads are 2.7–4.8 times today's list, and every one but Unavailable is linear in project size. At 215k that's 1.75–3.5 s per filtered page and 2.1–2.6 s per counts call, against 0.65–0.75 s for the unfiltered list, measured locally where everything is cached.
  • The counts endpoint is also what #4770 puts on the Searches page and Overview, so it would run on every visit to either page.
  • studiesWorkspace decides globally, so it can't be switched on only for small projects.
  • A stored classification (C) is the only alternative measured that brings every option and the counts below today's list. The lever on the counts (A) still leaves them at about twice the list, and leaves No PDF, Not yet confirmed and Without a confirmed PDF as they are.

Production is unaffected today. A read-only check of production on 5 November 2026 found that syrftest.pmStudyPdfAvailability doesn't exist, so migration 0034 hasn't run there. The PDF status admission is therefore closed, and neither the filter nor the counts can read anything there, whatever the flags say.

What the follow-up needs. The stored classification has its own flag and migrations. It's a new pmStudy field and new indexes, so it needs a backfill and ADR-023 index migrations. The field is written on hot study paths (PDF set and clear, bulk delivery, W2 accept, import) and by every check result, which is the "backend-only, hot path" default for a flag in .claude/rules/feature-flags.md. Its spec has to keep three properties:

  • The writer runs whatever the flag says, through targeted $set only, as slice 8's PdfAvailability does, so the stored field is never stale after an off, on, off round trip. The flag gates only the read. Like the 0034 admission, the read refuses to switch on until the backfill and indexes exist.
  • Configuration. The class depends on the configured legacy and bulk bases, through the link key and the proven bulk base. The stored value carries a fingerprint of that configuration. When the fingerprint doesn't match, the read falls back to today's C# path or recomputes.
  • Checks in progress (as decided). Checking depends on the 2-minute lease. During a lease, a study whose current document is Available reports Checking in the column, while a stored "Available" would match the Available filter. The read corrects the page and the counts from the project's availability documents with a fresh lease. There are few of them, and they're read through an index. That keeps #4719's parity with the column.

Stored classification (PR 3, #4849)

The same harness, extended for the stored classification (criterion 11 of the implementation plan's "Stored PDF classification (#4771)"). Measured on 5 November 2026 on the PR 3 branch, locally, as above. After timing today's C# path on the seeded project, the harness:

  1. creates migrations 0036 and 0037 directly in its database (the harness builds them by hand, as PR 4 (#4873) now registers them for real), which arms the writers there;
  2. runs the backfill over every study and times it;
  3. switches studyPdfStatusStored on, with one StudyPdfStoredAdmission serving every request, as the API's singleton does;
  4. times and profiles every served read again. Each must give the C# path's count exactly, and each did.

Results at 215k

Median wall time in ms for the whole controller action, five runs, in two separate processes (load 8–12 for the stored reads). The ×-figure is the ratio to the same run's unfiltered list. "Today" is the C# path in the same run, before arming.

Read Run A, stored Run B, stored Run A, today Run B, today
Unfiltered list 694 694 670 638
Without a confirmed PDF 777 (1.12×) 797 (1.15×) 2,688 3,122
Available 428 (0.62×) 478 (0.69×) 1,799 1,751
Not yet confirmed 374 (0.54×) 291 (0.42×) 2,756 2,729
Unavailable 307 (0.44×) 349 (0.50×) 601 612
No PDF 598 (0.86×) 548 (0.79×) 2,926 2,931
Search counts 467 (0.67×) 449 (0.65×) 2,284 2,273

Every option and the counts are 2 to 9 times faster than today (Unavailable, already fast, about 2 times). Every one but Without a confirmed PDF is at or below the unfiltered list. Without is 1.12–1.15 times the list, so criterion 11 is not met for that option; see below.

Database work at 215k, from the profiler (run B):

Read Docs examined Keys examined DB ms (sum)
Unfiltered list 210,700 632,100 637
Without a confirmed PDF 165,550 539,634 807
Available 53,775 316,081 405
Not yet confirmed 32,256 273,071 377
Unavailable 15,075 238,682 269
No PDF 120,400 449,350 574
Search counts 4,300 217,129 433

Each option's page reads 0037's index {ProjectId, PdfLinkKind, PdfCheckState, SystematicSearchId} and examines its matches, because the title sort fetches them, as the unfiltered list fetches the whole project. Its count reads 0037's keys. The counts examine no study: their 4,300 documents are the 2,150 in-progress checks (0036's index) and the narrowing of the checking ids. No read collection-scans.

Where Without's time goes

Without matches 75% of the project (161,250 of 210,700), and its page sorts by title like every page. No index provides that order, so the page fetches every match and sorts them. Its parts, run B (median ms):

Part ms
Unfiltered page by title ({ProjectId} scan, 210,700 fetched) 481
Without's page as served: 0037 scan, 161,250 fetched 425
Without's page with all 2,150 checking ids in an $or on _id (worst case) 615
The same, hinted to {ProjectId} 460
Without's count, all 2,150 checking ids (two signed parts) 77
The in-progress checks (0036) 12
  • The checking ids are narrowed first. A checking study needs the D9 correction only when its stored state is one the option would otherwise count differently. For Without that's a current Available result. So the read first finds which checking ids are in those states (at most one document per id), and usually none are. With none, the page and count carry no _id term. That took Without from 1.42 times the list to 1.12–1.15 times.
  • The worst case. In the timing mix, the 1% checking studies have no result, so nothing is corrected. A confirmed study being rechecked brings the $or back for that request. If all 2,150 were, the page would cost about 190 ms more. In practice a lease is held only while its check runs, and StudyPdfCheckWorker runs at most 4 checks at a time per API pod, so the corrected set is a handful of ids.
  • What's left is the fetch order. The 0037 scan fetches its matches in index order, not insertion order, and filtering on fields at the end of a 1 KB document costs per document. Hinting the {ProjectId} scan gives no gain (460 against 425). Closing the remaining gap would need the sort served by an index, which no PDF filter has today.

Backfill and indexes

Step Run A Run B
Create 0036 and 0037 (fields not yet written) 1.0 s 1.0 s
Backfill 236,500 studies (both projects), batches of 500, no pause 98.7 s 99.8 s

That's about 2,400 studies a second, with 0037 maintained as the fields are written. The worker's default 200 ms pause between batches adds about 95 s at this size: under four minutes for the largest project's database, once, per epoch.