Skip to content
Source
Sign inSign up
TanStack/db

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

Send to my agent
Closedsincraianul(GitHub profile, opens in a new tab) opened this issue 2 comments
  • 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):

usersno explicit indexusers.createIndex(r => r.id)
25,00039.4 ms4.3 ms
50,00064.4 ms—
100,000115.5 ms—
200,000243.4 ms4.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: #1700 (size-based suggestion gated on autoIndex) and #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.
  1. I'd like to take this as well — looking into teaching the join planner to recognize the collection key as an implicit index so the FK → PK join no longer falls back to a full scan.

  2. Thanks for the detailed report and reproducible measurements. We confirmed the unindexed join scans the joined collection: a 2,000-user case visits all 2,000 entries, while an explicit index on users.id visits zero.

    For now, we are keeping the explicit index as the supported way to make this join efficient:

    import { BTreeIndex } from '@tanstack/db'
    
    users.createIndex((row) => row.id, { indexType: BTreeIndex })

    The current runtime warning names the missing field and recommends createIndex, and the API is documented at https://github.com/TanStack/db/blob/main/docs/reference/interfaces/Collection.md#createindex. A Collection's getKey callback can compute a storage key that differs from the joined row field, so its key map cannot always replace an index on that field. We are closing this issue with the explicit-index guidance. We appreciate the report; it made the performance tradeoff concrete.

Sign in to join this conversation.