Why OceanBase 4.3.x Vector Indices Trigger Full Table Scans

If you're building a multi-tenant RAG system on OceanBase 4.3.x (MySQL mode), you might have encountered a frustrating performance cliff when combining scalar filters with vector similarity searches. In theory, a hybrid query should be efficient; in practice, the optimizer sometimes makes a decision that kills your latency.

The scenario is a classic production pattern: a table containing 1536-dimensional embeddings (stored as FLOAT arrays) using a native VSAG (Vector Similarity Search) index. To ensure data isolation, we use a tenant_id integer column indexed via a standard B-tree, alongside a status flag.

Consider the following query pattern:

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;

On paper, the execution plan should be simple: the engine filters by tenant_id and status, narrows the result set to a few thousand rows, and then performs the ANN (Approximate Nearest Neighbor) search on that subset. However, we've observed that as the dataset grows into the millions of rows, OceanBase occasionally ignores the B-tree index on tenant_id and triggers a full table scan.

The root of the issue lies in how the optimizer weighs the cost of the VSAG index versus the scalar filter. When the WHERE clause is combined with APPROX_TOP, the engine sometimes decides that scanning the entire table is "cheaper" than jumping between a B-tree index and the vector index. This results in a massive spike in I/O and latency, turning a sub-100ms query into a multi-second ordeal.

To diagnose this, run an EXPLAIN on your query. If you see TABLE SCAN instead of INDEX LOOKUP for your tenant filter, you're hitting this bottleneck.

One workaround we've found is to force the optimizer's hand. While OceanBase doesn't always support traditional index hints for vector operations in the same way it does for B-trees, you can attempt to restructure the query or adjust the optimizer_trace to see why the scalar filter is being discarded. In some 4.3.x builds, the cost model for APPROX_DISTANCE is overly optimistic, leading the engine to believe a full scan is more efficient than a filtered index lookup.

If you are seeing this behavior, I recommend checking your statistics. Run ANALYZE TABLE enterprise_knowledge_base; to ensure the optimizer has an accurate count of the distribution of tenant_id. If the cardinality is high, the optimizer should theoretically favor the B-tree; if it doesn't, you may be dealing with a cost-estimation bug specific to the VSAG implementation.

For those scaling to millions of embeddings, this "filter-then-search" efficiency is the difference between a viable product and a system that crashes under load. Keep a close eye on your execution plans when mixing scalar and vector predicates.