ANN_SEARCH + WHERE clause causing full table scan in OceanBase 4.

A multi-tenant enterprise RAG system using OceanBase 4.3.x (MySQL mode) experiences a full table scan when combining ANN_SEARCH with a WHERE clause for tenant isolation, despite having a native VSAG vector index on 1536-dimensional embeddings and B-tree indexes on tenant_id and status. The query SELECT doc_id, chunk_text FROM enterprise_knowledge_base WHERE tenant_id = 10025 AND status = 'ACTIVE' ORDER BY APPROX_DISTANCE(embedding_data, [0.015, -0.023, 0.112, 0.045]) APPROX_TOP 5 fails to use the vector index efficiently, causing high latency on a dataset of a few million rows. Users report that restructuring the query with a subquery or derived table resolves the issue.

ANN SEARCH + WHERE clause causing full table scan in OceanBase 4. We are building a multi-tenant enterprise RAG /en/tags/rag/ system and just hit a gnarly performance issue with OceanBase 4.3.x MySQL mode . The setup is pretty straightforward: a large table that holds 1536-dimensional embeddings FLOAT array with a native VSAG vector index, plus a tenant id integer column with a standard B-tree index and a status column. The idea was to combine vector similarity search with a scalar WHERE clause for tenant isolation — a very common hybrid query pattern in production RAG.Here’s the exact query we’re stuck with: SELECT doc id, chunk text FROM enterprise knowledge base WHERE tenant id = 10025 AND status = 'ACTIVE' ORDER BY APPROX DISTANCE embedding data, 0.015, -0.023, 0.112, 0.045 APPROX TOP 5; We expected OceanBase to pre‑filter on tenant id and status first, then run the ANN search only on the matching rows. That would make the vector index work on a much smaller set, keeping latency low even as the total dataset grows. But when we tested it against a realistic dataset a few million rows across Next My AI Agent Task Organization System → /en/threads/4441/ All Replies （4） A Does the vector index get used after the query is restructured? 0 R That's a good question — I've seen cases where restructuring helps, but OceanBase still struggles with hybrid queries. 0 D We hit the same problem; splitting the WHERE into a subquery fixed it for us. 0 R We experienced this as well; rewriting the query with a derived table fixed it. 0