Join inside a correlated include ignores the subquery's where filter — scans the whole source collection

[sincraianul]

• Validated against @tanstack/db@0.6.17 (Node 24.18.0)

Summary

When a correlated subquery nested in .select() (an "include") contains a .join(), the subquery's own where no longer bounds what the join scans. The mount cost becomes linear in the size of the joined-from collection rather than in the rows the where selects.

The pushdown demonstrably works without the join — that is the control below, and it stays flat. Adding a join to the same subquery, with the same predicate and indexes on every join column, makes it linear.

Measurements

Identical query, identical 6-row result throughout. Only the links collection grows, and every added row belongs to a group the query never selects:

links collection	include only	include + join	rows returned
6	5.6 ms	1.7 ms	6 / 6
50,006	1.0 ms	107.9 ms	6 / 6
200,006	0.9 ms	295.2 ms	6 / 6

Indexes are declared on all four join/correlation columns (meanings.termId, groups.meaningId, links.groupId, links.targetId) and on terms.id.

A CPU profile of the same shape in a browser at 212k rows attributes the time to currentStateAsChanges (collection/change-events.js, which iterates collection.entries() in full) and wrapInputWithAlias (which object-rest-spreads every row), plus the resulting GC — i.e. the whole collection is being materialised into the dataflow graph.

Reproduction

// node repro.mjs
import {
  BTreeIndex, createCollection, createLiveQueryCollection, eq,
  localOnlyCollectionOptions, materialize,
} from '@tanstack/db'

const TERMS = 50_000

const collection = (id, rows) =>
  createCollection(localOnlyCollectionOptions({ id, getKey: (r) => r.id, initialData: rows }))

async function build(filler) {
  const terms = collection(`terms-${filler}`,
    Array.from({ length: TERMS }, (_, i) => ({ id: `t${i}`, text: `word-${i}` })))
  const meanings = collection(`meanings-${filler}`, [{ id: 'm0', termId: 't0' }])
  const groups = collection(`groups-${filler}`, [
    { id: 'g0', meaningId: 'm0' },
    { id: 'g-filler', meaningId: 'm-never-queried' },
  ])
  const links = collection(`links-${filler}`, [
    ...Array.from({ length: 6 }, (_, i) => ({ id: `l${i}`, groupId: 'g0', targetId: `t${i + 1}` })),
    // never selected by the query — only makes the collection bigger
    ...Array.from({ length: filler }, (_, i) => ({ id: `f${i}`, groupId: 'g-filler', targetId: `t${i % TERMS}` })),
  ])
  await Promise.all([terms.preload(), meanings.preload(), groups.preload(), links.preload()])
  meanings.createIndex((r) => r.termId, { indexType: BTreeIndex })
  groups.createIndex((r) => r.meaningId, { indexType: BTreeIndex })
  links.createIndex((r) => r.groupId, { indexType: BTreeIndex })
  links.createIndex((r) => r.targetId, { indexType: BTreeIndex })
  terms.createIndex((r) => r.id, { indexType: BTreeIndex })
  return { terms, meanings, groups, links }
}

// Differs from the control ONLY by the .join(...) line.
const withJoin = ({ terms, meanings, groups, links }) =>
  createLiveQueryCollection((q) =>
    q.from({ t: terms }).where(({ t }) => eq(t.id, 't0')).select(({ t }) => ({
      id: t.id,
      meanings: materialize(
        q.from({ m: meanings }).where(({ m }) => eq(m.termId, t.id)).select(({ m }) => ({
          id: m.id,
          groups: materialize(
            q.from({ g: groups }).where(({ g }) => eq(g.meaningId, m.id)).select(({ g }) => ({
              id: g.id,
              links: materialize(
                q.from({ l: links })
                  .where(({ l }) => eq(l.groupId, g.id))          // selects 6 rows
                  .join({ tgt: terms }, ({ l, tgt }) => eq(l.targetId, tgt.id), 'inner')
                  .select(({ l, tgt }) => ({ id: l.id, text: tgt.text })),
              ),
            })),
          ),
        })),
      ),
    })),
  )

for (const filler of [0, 50_000, 200_000]) {
  const c = await build(filler)
  const started = performance.now()
  const q = withJoin(c)
  await q.preload()
  console.log(filler + 6, `${(performance.now() - started).toFixed(1)}ms`,
    q.toArray[0].meanings[0].groups[0].links.length, 'rows')
}

Dropping the .join(...) and selecting l.targetId instead gives the flat control column.

Expected

The where on the subquery's from should bound the join's input as it does without the join, so cost tracks the selected rows rather than the collection.

Impact

This is the FK→PK shape inside a hierarchical read — one parent row fanning out to a handful of children, each carrying a label from another table. In our app it means opening one dictionary entry (6 groups, 36 links) streams all 212,041 rows of the links collection, ~340 ms, growing as the user adds data.

Note

Distinct from #1708, which I filed earlier for the join's right side (a collection's own key is not usable as an index; 144x when the left side is small). This is the left side, and at realistic collection sizes it is the dominant term — with terms.id indexed the 200k case is still 376 ms, versus 595 ms without.