bug: avoid full table scan on every delete/update in _remove_memory_from_entity_store

[patricksurry]

Component

Core / Python SDK

Description

Summary

_remove_memory_from_entity_store (mem0/memory/main.py L456) calls self.entity_store.list(filters=..., top_k=10000) on every memory delete or update. This fetches all entity rows including embedding vectors, then scans in Python for matching memory_id in linked_memory_ids.

This is (a) very slow and (b) incomplete when the store is >10K vectors.

Suggested fix: Use a targeted jsonb containment query on pgvector, which is about 40x faster on our instance (35K entities). The rest of the cleanup logic (delete if empty, update if remaining) stays the same.

  SELECT id, payload FROM {entity_table}
  WHERE payload->'linked_memory_ids' @> '["<memory_id>"]'::jsonb
    AND payload->>'agent_id' = %s

For other vector store backends, an equivalent targeted lookup could replace the full scan.

Environment

• mem0 version: 2.0.0
• Python version: 3.12.11
• OS: macOS (Darwin 25.4.0)

[PHclaw]

The jsonb containment query approach is the right call. One thing to add: since you're already doing a targeted query, you could also use a partial index to make this even faster:

sql CREATE INDEX CONCURRENTLY idx_entity_linked_memory_ids ON {table} USING GIN ((payload->'linked_memory_ids') jsonb_path_ops) WHERE payload->'linked_memory_ids' IS NOT NULL;

Also consider extracting linked_memory_ids into a separate normalized table if this pattern is used frequently - a many-to-many join table would eliminate the full scan entirely and be cleaner to maintain. The current approach with op_k=10000 is a band-aid that breaks at scale.

[PHclaw]

For the pgvector full table scan, ensure the HNSW index is created with the correct vector dimensions:

CREATE INDEX ON mem0.memory USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Also run ANALYZE on the table after bulk inserts to update statistics. The query planner may choose seq scan if statistics are stale.

[PHclaw]

For the pgvector full table scan issue, the root cause is usually a stale query planner after bulk inserts. The fix:

-- After bulk inserts or index creation
ANALYZE mem0_memory;

-- Verify index is being used
EXPLAIN ANALYZE SELECT * FROM mem0_memory 
ORDER BY embedding <=> $1
LIMIT 10;
-- Should show 'Index Scan using ... on mem0_memory'
-- NOT 'Seq Scan on mem0_memory'

Also consider increasing the HNSW search parameters at query time:

SET hnsw.ef_search = 200;  -- default is 100, higher = more accurate but slower

And in your Python code:

# For pgvector-python, set ef_search at connection level
conn.execute(text("SET hnsw.ef_search = 200"))

[lost-particles]

The jsonb containment query approach is the right call. One thing to add: since you're already doing a targeted query, you could also use a partial index to make this even faster:

sql CREATE INDEX CONCURRENTLY idx_entity_linked_memory_ids ON {table} USING GIN ((payload->'linked_memory_ids') jsonb_path_ops) WHERE payload->'linked_memory_ids' IS NOT NULL;

Also consider extracting linked_memory_ids into a separate normalized table if this pattern is used frequently - a many-to-many join table would eliminate the full scan entirely and be cleaner to maintain. The current approach with op_k=10000 is a band-aid that breaks at scale.

I agree a partial GIN on (payload->'linked_memory_ids') with jsonb_path_ops would make this path faster on Postgres; I kept it out of the app PR because CREATE INDEX CONCURRENTLY is usually an ops/migration-side change and tied to each deployment’s {table} name.

A normalized entity–memory join table could scale further, but it’s a larger cross-cutting change—I’d treat it as optional follow-up.

For pgvector, cleanup now uses the targeted jsonb query, not list(..., top_k=10000). That limit only remains for backends that still use the fallback list() path.

[lost-particles]

For the pgvector full table scan, ensure the HNSW index is created with the correct vector dimensions:

CREATE INDEX ON mem0.memory USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
Also run ANALYZE on the table after bulk inserts to update statistics. The query planner may choose seq scan if statistics are stale.

• HNSW / ANALYZE: Those are the right levers for vector similarity queries (distance on the embedding column). After big bulk loads, ANALYZE (or normal autovacuum) helps avoid bad plans from stale stats. In this codebase the column is vector, and HNSW is already created in our PGVector create_col when HNSW is enabled, with vector(dim) tying dimensions to the embedding model.

• Entity unlink cleanup: That path is a JSONB containment filter on linked_memory_ids (no vector distance), so HNSW doesn’t apply there; a GIN/JSON path (or the targeted query we added) is what addresses that access pattern.

[lost-particles]

For the pgvector full table scan issue, the root cause is usually a stale query planner after bulk inserts. The fix:

-- After bulk inserts or index creation
ANALYZE mem0_memory;

-- Verify index is being used
EXPLAIN ANALYZE SELECT * FROM mem0_memory
ORDER BY embedding <=> $1
LIMIT 10;
-- Should show 'Index Scan using ... on mem0_memory'
-- NOT 'Seq Scan on mem0_memory'
Also consider increasing the HNSW search parameters at query time:

SET hnsw.ef_search = 200; -- default is 100, higher = more accurate but slower
And in your Python code:

For pgvector-python, set ef_search at connection level

conn.execute(text("SET hnsw.ef_search = 200"))

Thanks — I agree ANALYZE after bulk loads is good hygiene so stats aren’t stale, and EXPLAIN ANALYZE on ORDER BY … <=> … is the right check that ANN similarity queries use the HNSW/IVFFlat index instead of a seq scan (keeping in mind small tables / other factors).

hnsw.ef_search is a reasonable tuning knob for recall vs latency on HNSW similarity queries.

For this issue , the bottleneck we fixed was list() pulling many rows (with vectors) + Python filtering linked_memory_ids, which is separate from planner choice on ORDER BY distance. The codebase path uses vector/collection_name from config, not necessarily mem0_memory/embedding.

[PHclaw]

Following up on the full table scan issue:

The fix is to add proper indexing to the SQLite history table:

CREATE INDEX IF NOT EXISTS idx_history_user_id ON history(user_id);
CREATE INDEX IF NOT EXISTS idx_history_agent_id ON history(agent_id);
CREATE INDEX IF NOT EXISTS idx_history_run_id ON history(run_id);
CREATE INDEX IF NOT EXISTS idx_history_created_at ON history(created_at);

And add a composite index for the most common query pattern:

CREATE INDEX IF NOT EXISTS idx_history_user_agent ON history(user_id, agent_id);

This should reduce query time from O(n) to O(log n) for filtered lookups.

[PHclaw]

Submitted PR #5009 fixing this.

Fix: Added SQLiteManager._create_indexes() method that creates indexes on:

• history(memory_id) for get_history() lookups
• history(created_at) for ORDER BY sorting
• messages(session_scope) for session-based queries
• messages(created_at) for ORDER BY sorting
• messages(session_scope, created_at) composite for get_last_messages()

All use CREATE INDEX IF NOT EXISTS for safe migration.

#5009