Performance: 'has records' subject filter reads every instrument record to compute a distinct subject list

[gdevenyi]

Current Implementation and Limitations

The datahub's "With records only" filter is implemented by reading every instrument record in scope into the API process, deduplicating subject ids in JavaScript, and then sending them all back to MongoDB as an $in array.

SubjectsService.querySubjectIdsWithRecords (apps/api/src/subjects/subjects.service.ts:161):

private async querySubjectIdsWithRecords(groupId?: string): Promise<string[]> {
  const records = await this.prismaClient.instrumentRecord.findMany({
    distinct: ['subjectId'],
    select: { subjectId: true },
    where: groupId ? { groupId } : {}
  });
  return records.map((r) => r.subjectId);
}

consumed at :139:

return this.subjectModel.findMany({
  where: {
    AND: [
      accessibleQuery(ability, 'read', 'Subject'),
      groupInput,
      { id: { in: await this.querySubjectIdsWithRecords(groupId) } }
    ]
  }
});

Two problems:

1. distinct is not pushed into MongoDB. Prisma applies distinct in the query engine, not as a server-side $group/distinct command, so the driver receives one document per record, not per distinct subject. The deduplication to a much smaller set happens only after all of them have crossed the wire.
2. The result is round-tripped back as a literal $in array. Every subject id that has any record is serialised into the second query. On a large group this is a query document containing thousands of strings.

Also note where: groupId ? { groupId } : {} ignores the caller's ability entirely — for the unscoped (root) case this reads the whole InstrumentRecordModel collection, regardless of what the requesting user is permitted to see.

This is the issue previously raised as #1346 ("Improve initial hasRecord filter performance", closed). The current implementation still has the shape described above.

Measurements

MongoDB 8, 200,000 instrument records across 8 groups, 5,000 subjects:

scope        docs examined   docs returned to Prisma   distinct subjects   time
group            200,000              25,000                  625          62 ms   (no index)
group             25,000              25,000                  625          42 ms   (with index)
root (all)       200,000             200,000                5,000         ~85 ms   (no index)

So for the group case, Prisma transfers 25,000 documents to compute a 625-element result, then issues a second query whose where clause embeds those 625 ids. For the unscoped case it transfers 200,000 documents to compute 5,000 ids. The ratio of transferred documents to useful output is 40:1 and grows with records-per-subject.

Indexing {groupId, date} (see the indexing issue) fixes the scan but not the transfer — the 25,000 documents still cross the wire.

Associated Application Components

Server

Proposed Solution

1. Do the distinct in the database. A $group aggregation returns one document per subject instead of one per record:

private async querySubjectIdsWithRecords(groupId?: string, ability?: AppAbility): Promise<string[]> {
  const result = await this.prismaClient.instrumentRecord.aggregateRaw({
    pipeline: [
      { $match: groupId ? { groupId: { $oid: groupId } } : {} },
      { $group: { _id: '$subjectId' } }
    ]
  });
  return (result as unknown as { _id: string }[]).map((r) => r._id);
}

With the {groupId, date} index this is a covered-ish scan and returns 625 documents instead of 25,000 — a 40× reduction in data transferred, for the same result.

2. Better still, avoid the round trip entirely. Prisma can express "subject has at least one record" as a relation filter, so the two queries collapse into one and no id list is ever materialised in the application:

return this.subjectModel.findMany({
  where: {
    AND: [
      accessibleQuery(ability, 'read', 'Subject'),
      groupInput,
      { instrumentRecords: { some: groupId ? { groupId } : {} } }
    ]
  }
});

This is the version worth trying first — it is simpler than the current code, not more complex. It is worth explain()-ing on a realistic dataset before committing, since MongoDB relation filters in Prisma can compile to a $lookup, and the aggregation in (1) is the fallback if the plan is worse.

3. Respect the ability. querySubjectIdsWithRecords should apply accessibleQuery(ability, 'read', 'InstrumentRecord') to its own where, matching every other query in the service. Currently a request that resolves to the unscoped branch reads records the user has no permission to read (their ids are then intersected away by the outer query, so this is not a data leak, but it is unnecessary work and an inconsistency).

Estimated Difficulty

Medium

Priority

Medium

[joshunrau]

@gdevenyi claude can do this

[gdevenyi]

Correction: option 2 in this issue is wrong, and I recommended it first — don't take it.

I suggested expressing the filter as instrumentRecords: { some: ... } on Subject and called it "the version worth trying first". Measured on 200,300 records:

findMany distinct (current impl), scoped        197ms  -> 55 ids
findMany distinct (current impl), unscoped     1229ms  -> 100 ids
groupBy by subjectId, scoped                     95ms  -> 55 ids
groupBy by subjectId, unscoped                  100ms  -> 100 ids
subject.findMany({instrumentRecords:{some}})   26849ms  -> 55 subjects

The relation filter is 270× slower than the code it would replace. Prisma compiles it into a $lookup over the whole record collection — the same construct that fails outright at scale in #1416, with the same 100 MiB per-document ceiling.

#1426 uses groupBy instead, which pushes the distinct into mongodb and is flat in records-per-subject. Over HTTP: GET /v1/subjects?hasRecord=true 366ms → 42ms.

It also fixes item 3 — querySubjectIdsWithRecords now applies the caller's ability, which it previously ignored.