Joining on a collection's own key falls back to a full scan unless an explicit index on that field is created

[sincraianul]

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

Summary

A collection is a keyed map — getKey is mandatory, collection.get(key) is O(1), collection.state is a Map<TKey, T>. But the join planner does not recognise the key as an index. Joining on the key field falls back to Falling back to loading all data, and the mount cost becomes linear in the size of the joined collection unless you manually createIndex((row) => row.id) — an index over the exact field the collection is already keyed by.

This is the FK → PK join, i.e. the most common join shape in any normalized schema, so the fallback is easy to hit and the fix is a redundant index.

Measurements

10 posts inner-joined to N users on users.id, timing preload() of the live query (repro below):

users	no explicit index	users.createIndex(r => r.id)
25,000	39.4 ms	4.3 ms
50,000	64.4 ms	—
100,000	115.5 ms	—
200,000	243.4 ms	4.0 ms

Unindexed is linear in collection size; indexed is flat. The query produces 10 rows either way.

The warning does fire and names the field correctly:

[TanStack DB] [users] Join requires an index on "id" for efficient loading. Falling back to loading
all data. Consider creating an index on the collection with collection.createIndex((row) => row.id)

So the planner knows it wants an index on id — it just doesn't know the collection already has one, by construction.

Reproduction

// node repro.mjs          -> WITHOUT explicit index on users.id
// INDEX=1 node repro.mjs  -> WITH
import {
  BTreeIndex, createCollection, createLiveQueryCollection, eq, localOnlyCollectionOptions,
} from '@tanstack/db'

const USERS = Number(process.env.USERS ?? 50_000)

const users = createCollection(localOnlyCollectionOptions({
  id: 'users',
  getKey: (row) => row.id,
  initialData: Array.from({ length: USERS }, (_, i) => ({ id: `u${i}`, name: `name-${i}` })),
}))
const posts = createCollection(localOnlyCollectionOptions({
  id: 'posts',
  getKey: (row) => row.id,
  initialData: Array.from({ length: 10 }, (_, i) => ({ id: `p${i}`, userId: `u${i}` })),
}))

await users.preload()
await posts.preload()

// The collection is ALREADY a keyed map on this exact field:
console.log(users.get('u42'))   // O(1), no index declared

if (process.env.INDEX === '1') {
  users.createIndex((row) => row.id, { indexType: BTreeIndex })
}

const started = performance.now()
const q = createLiveQueryCollection((qb) =>
  qb
    .from({ p: posts })
    .join({ u: users }, ({ p, u }) => eq(p.userId, u.id), 'inner')
    .select(({ p, u }) => ({ id: p.id, name: u.name })),
)
await q.preload()
console.log(`${(performance.now() - started).toFixed(1)}ms, ${q.toArray.length} rows, ${USERS} users`)

Expected

An equality join whose predicate targets the joined collection's own key field should use the existing key map rather than a full scan — no user-declared index required, and no warning.

Notes

• Same behaviour for a join nested inside a correlated select() subquery (an "include"), where it is more costly because the include is instantiated per parent row.
• autoIndex: 'eager' + defaultIndexType is a workaround, but it opts the collection into auto-indexing every queried field, which is a much broader change than "use the key you already have".
• Related but distinct: Index suggestion for collection size is gated on autoIndex, so it only fires where it is redundant #1700 (size-based suggestion gated on autoIndex) and Lazy-join Join requires an index warning blames the wrong collection when a subquery's select field traces across an inner join #1494 (join warning naming the wrong collection) are both about warning quality. This one is about the optimisation itself — here the warning is correct and the fallback is real.