Denormalise knowledge-base id into embeddings table for pre ANN filtering

[vizsatiz]

Title

KB-scoped vector search (CLIP/DINO) can silently return incomplete results due to post-filter HNSW behavior on multi-tenant embeddings table

Summary

get_image_embedding_clip / get_image_embedding_dino in generate_query.py filter by knowledge_base_id via a join to knowledge_base_documents, then order by the HNSW-indexed <=> distance on knowledge_base_embeddings. Because pgvector applies WHERE-clause filtering after the HNSW index scan, and the filter column lives on a joined table rather than the embeddings table itself, the planner cannot push the KB filter into the ANN walk. This can silently return fewer than top_k results (or zero) for a knowledge base whose vectors aren't among the globally-nearest candidates in the shared, multi-tenant knowledge_base_embeddings table.

Root cause

• knowledge_base_embeddings is shared across all knowledge bases; the only KB scoping is via a JOIN to knowledge_base_documents.knowledge_base_id.
• pgvector's HNSW index scans in distance order and returns up to hnsw.ef_search candidates before any WHERE/JOIN filter is applied (confirmed against pgvector docs: "filtering is applied after the index is scanned").
• ef_search is set via compute_ef_search() (generate_query.py:27), floored at 200 — but those 200 candidates are the globally-nearest vectors across all KBs, not the nearest vectors within the target KB.
• If a KB is a small slice of a large shared table, its true nearest neighbors may not appear in that global top-200, so the post-join filter can drop below top_k results (or return none) even though matching rows exist.
• hnsw.iterative_scan (pgvector ≥0.8, would auto-expand the scan until enough post-filter matches are found) is not configured anywhere in the codebase.
• pgvector's own guidance for this shape of problem is to index/filter on a column in the same table as the vector column — not achievable here since knowledge_base_id lives on the joined knowledge_base_documents table, not on knowledge_base_embeddings.

Impact

• Correctness/recall bug, not a performance bug: queries can return incomplete or empty result sets without any error, for knowledge bases that are small relative to the total shared embeddings table.
• Severity scales with total row count in knowledge_base_embeddings and the number/size distribution of tenants (KBs) sharing it — worse as the platform grows.
• Affects both image_retrieve_clip and image_retrieve_dino in image_rag_retrieve.py.

Proposed fix (pick one, not mutually exclusive)

1. Denormalize knowledge_base_id onto knowledge_base_embeddings directly (avoid the join for filtering). This puts the filter column on the same table as the vector column, matching pgvector's documented recommendation, and enables:
   • A plain b-tree index on knowledge_base_embeddings.knowledge_base_id, and/or
   • Partial HNSW indexes per hot/large KB (... WHERE (knowledge_base_id = X)), if a small number of KBs dominate row count.
2. Enable iterative index scans (SET LOCAL hnsw.iterative_scan = relaxed_order) alongside the existing SET LOCAL hnsw.ef_search call in sql_alchemy_repository.py, so Postgres auto-expands the scan until enough post-filter matches are found. Requires confirming the deployed pgvector extension version is ≥0.8 (currently unpinned via ankane/pgvector:latest).
3. Table partitioning of knowledge_base_embeddings by knowledge_base_id, if the number of distinct KBs is manageable — likely overkill unless (1) proves insufficient.

Recommend starting with (1) + (2) together: denormalize for correctness and index efficiency, enable iterative scan as a safety net for recall.

References

• pgvector filtering docs: https://github.com/pgvector/pgvector#filtering
• Related: separate missing-index issue for knowledge_base_documents.knowledge_base_id and knowledge_base_embeddings.document_id (join scan cost) — tracked separately.