Join inside a correlated include ignores the subquery's where filter — scans the whole source collection #1709
Send to my agent- 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.
tannerlinsley closed this
Sign in to join this conversation.
Taking this one (same series as #1708/#1746). Plan: reproduce the linear-scan mount, trace why the subquery's own
wherestops bounding the join's left input when a.join()is present inside a correlated include, and push the bound down to the snapshot request the way the no-join control already does.