Fixes #1708.

The problem

Joining on a collection's own key (the classic FK → PK join) fell back to a full collection scan unless the user created an explicit index on that field — even though the collection's keyed state can already answer key lookups in O(1). The lazy-join loader would emit the Join requires an index on "id" warning and load the entire collection.

The fix

Query optimization now falls back to a synthetic KeyIndex when no user-created index matches the field being looked up:

• CollectionImpl lazily derives a keyIndex from config.getKey, using the same ref-proxy introspection that createIndex uses for its callback: when getKey is a plain property access (e.g. (row) => row.id), that property becomes the indexed field. Composite/computed keys (or a getKey that throws on the proxy) simply yield no key index, preserving today's behavior.
• KeyIndex extends BaseIndex but stores nothing — eq/in lookups delegate to collection.has(), so it needs no maintenance and is always exactly in sync with collection state. It reports support for eq/in only, so order-by (which requires gt support) and range predicates are unaffected and keep their existing fallbacks.
• findIndexForField consults collection.keyIndex only after the explicit-index loop, so a user-created index on the key field always wins, and the same matchesCompareOptions check applies (collections with custom collation conservatively skip the key index).

With this, join(..., eq(other.id, item.otherId)) on an unindexed collection loads only the matching keys through requestSnapshot({ where: inArray(...), optimizedOnly: true }) — no warning, no full scan. optimizeInArrayExpression's existing exactness semantics apply unchanged, and lookup values are normalized the same way BasicIndex normalizes them, so behavior matches what an explicit index on the key field would do.

Tests

• tests/key-index.test.ts — unit coverage: derivation from getKey (single property / composite / non-property / throwing), lookup semantics (eq/in, unsupported ops throw), collection-level keyIndex reflecting live state, and findIndexForField precedence (explicit index wins; non-key fields, composite-key collections, and collections with a custom defaultStringCollation are conservatively unaffected).
• tests/query/join-key-index.test.ts — integration: a lazy join on the primary key with autoIndex: 'off' produces correct results with no "Join requires an index" warning, join keys appearing after the initial load are served incrementally, and a computed-key collection still warns (fallback path preserved). Both join tests fail on main and pass with this change.
• tests/query/indexes.test.ts — updated the three join-optimization tests whose premise ("no index on the join key") no longer holds when the join key is the primary key: the both-indexed and inner-join tests now assert the key-index-served load (no full scan), and the two "should not optimize" tests restore their premise with a computed key so the genuine full-scan fallback stays covered. The local index-usage tracker now also observes keyIndex lookups.
• Full @tanstack/db suite, build, and lint pass.

Design note: unsupported members

BaseIndex declares the full IndexInterface as abstract members, so KeyIndex has to implement range/ordered-access members it can never serve. They are one-line throwing stubs behind a single unsupported() helper: unreachable in practice (supports() reports only eq/in, and every call site — the optimizer's range paths and order-by's supports('gt') check — gates on it), and throwing keeps any future call path that does reach them loudly wrong instead of silently dropping rows. ReverseIndex pays the same interface-width cost today via pure delegation.

Longer-term, the cleaner fix for that interface bloat would be splitting IndexInterface into capability slices (equality / range / ordered access), so an index only declares what it actually supports and the supports() runtime checks get type-level backing. That changes the contract for every index type and consumer, so it's deliberately out of scope here — I'd be happy to take it on as a follow-up if maintainers are interested.

Summary by CodeRabbit

• New Features

  • Lazy joins can automatically use collection primary keys for faster matching.
  • Matching rows can load on demand instead of scanning an entire collection.
  • Key-based lookups support equality and multiple-value searches, including newly added records.

• Bug Fixes

  • Queries now avoid unsupported optimizations and correctly fall back to full scans when necessary.
  • Computed or composite keys continue to use appropriate fallback behavior.
  • Join optimization remains accurate as collection contents change.