Search hydrate_results N+1 query causes 3s+ latency on cloud

[phernandez]

Problem

to_search_results() in src/basic_memory/api/v2/utils.py makes a separate get_entities_by_id() call per search result, creating an N+1 query pattern. On Basic Memory Cloud where each DB round-trip goes to Neon PostgreSQL (~300ms network latency), this adds up fast.

For 10 results: 10 sequential round-trips × ~300ms = ~3s just to hydrate.

Evidence

Logfire data from today (60 search calls):

Phase	Avg	P50	P95	Max
execute_query	1.0s	0.96s	1.81s	5.76s
hydrate_results	0.98s	0.75s	2.88s	3.37s
paginate_results	0.001s	0s	0.001s	0.003s
build_response	0.001s	0.001s	0.001s	0.003s

Drilling into the slowest trace (019d49b0b62a1d112df71e20ec93aeb3):

• hydrate_results: 3.371s total
  • fetch_entities: 3.367s (10 sequential DB calls)
  • shape_results: 0.001s

Root Cause

src/basic_memory/api/v2/utils.py lines 188-192:

for result in results:
    entity_batches.append(
        await entity_service.get_entities_by_id(
            [result.entity_id, result.from_id, result.to_id]
        )
    )

Each iteration awaits a separate DB query. With Neon's network latency this is ~300ms per result.

Proposed Fix

Collect all unique entity IDs upfront, fetch them in a single find_by_ids call, then index into the result dict:

# Collect all entity IDs across all results
all_ids = set()
for result in results:
    for eid in (result.entity_id, result.from_id, result.to_id):
        if eid is not None:
            all_ids.add(eid)

# Single batch fetch
entities_by_id = {e.id: e for e in await entity_service.get_entities_by_id(list(all_ids))}

# Build results using the lookup dict
for result in results:
    entities = [entities_by_id[eid] for eid in (result.entity_id, result.from_id, result.to_id) if eid in entities_by_id]
    ...

This turns 10 round-trips into 1 (~300ms instead of ~3s).

Impact

Search is the slowest MCP tool on cloud (P50 ~3-6s). This fix would roughly halve search latency by eliminating ~1s avg / ~3s worst-case from hydration.

[claude]

Claude finished @phernandez's task in 1m 2s —— View job

---

Issue Triage: Search N+1 Query Performance

• Read issue and gather context
• Review relevant code
• Apply labels
• Check for duplicates
• Provide triage summary

---

Classification

Field	Value
Type	Bug / Enhancement — performance regression pattern
Priority	High — directly impacts cloud search UX with 3s+ latency
Component	Cloud, API
Complexity	Medium — well-scoped, proposed fix is clear

Triage Summary

Confirmed root cause: to_search_results() in src/basic_memory/api/v2/utils.py lines 188–193 has a classic N+1 query pattern — one get_entities_by_id() DB call per search result, executed sequentially. With Neon PostgreSQL's ~300ms network latency on cloud, 10 results = ~3s of hydration overhead.

No duplicates found.

Proposed fix is sound: Collecting all unique entity IDs upfront and issuing a single batch fetch (as described in the issue) is the correct approach. The fix is low-risk and well-contained to the to_search_results() function.

Suggested Fix Outline

1. Collect all unique IDs from (result.entity_id, result.from_id, result.to_id) across all results
2. Single await entity_service.get_entities_by_id(list(all_ids)) call
3. Build a {id: entity} lookup dict
4. Index into dict when building each SearchResult

This reduces N DB round-trips to 1, cutting worst-case hydration from ~3s to ~300ms.

Labels applied: bug, enhancement, cloud