Fixing hybrid vector-plus-filter query performance in production RAG without full table scans

Introduction / Context

Enterprise RAG systems rarely fail on the clean demo query. They fail on the real one: "find the nearest chunks for this question, but only inside tenant 42, only for documents the caller can access, only for the latest version, and maybe only in German." That is where vector retrieval stops being an ANN benchmark and turns into a query planning problem.

The bottleneck is not vector distance alone. It is the combination of ANN search with metadata predicates. In production, that usually means tenant isolation, ACLs, document status, retention windows, language, source system, or version flags.

If the database cannot apply those predicates before or during vector search, the engine drifts toward one of two bad plans:

1. scan a large candidate set from the vector index and discard most of it afterward, or
2. fall back to a full table scan because the optimizer cannot produce a workable hybrid plan.

That behavior is not hypothetical. A Stack Overflow report describes OceanBase 4.3.x hybrid ANN plus WHERE queries in a multi-tenant RAG deployment forcing full table scans instead of vector-index pre-filtering. Another report, on embedding failures for scientific PDFs, points at the other side of the same production story: retrieval quality and retrieval cost are coupled. Bad ingestion produces bad candidates; weak filter plans make even good embeddings unusable under load.

The practical takeaway is straightforward: if your RAG system depends on vector search plus selective metadata filters, design for filtered retrieval on purpose. Do not assume the SQL optimizer will discover the right plan from a generic schema.

The Question

How do we fix hybrid vector-plus-filter query performance in production RAG systems without falling back to full table scans?

For most teams, this is not a single tuning flag. It is a retrieval architecture choice.

The database has to answer two questions at once:

• Which rows are eligible?
• Among those rows, which are closest in embedding space?

If those are handled by separate access paths with no cheap intersection strategy, the engine has to choose where to pay. ANN-first can blow up candidate counts when the filter is selective. Filter-first can still be expensive when the filter leaves millions of rows. A naive optimizer often picks badly because vector selectivity estimates are weak, metadata distributions are skewed, and multi-tenant workloads create hot partitions.

So the useful version of the question is narrower: how do we structure data and queries so the eligible set is small enough, stable enough, and visible enough that vector search does not collapse into scanning the whole corpus?

The Answer

The shortest serious answer is this: put the filter boundary into the physical retrieval path.

That usually means one or more of these patterns:

1. Pre-partition by the hardest filter, usually tenant or security domain.
2. Search a bounded candidate set, using filter-first IDs or shards, then do ANN within that set.
3. Materialize coarse filter buckets into the vector-store layout, instead of leaving them as columns in a row store.
4. Split retrieval into stages, with an explicit candidate-generation step and a rerank step.
5. Keep high-cardinality ACL logic out of the hot ANN path unless the engine actually supports filtered vector indexes.

If I had to recommend one architecture for an enterprise RAG team today, it would be this:

Use tenant-scoped or security-domain-scoped vector partitions, plus a two-stage query plan: metadata filter to produce a bounded candidate universe, then ANN over only that universe, then rerank.

Why this works:

• It removes the optimizer's hardest choice. The search space is already restricted before distance ranking gets expensive.
• It matches real enterprise traffic. Tenant and access-control predicates are usually mandatory, stable, and selective.
• It makes latency more predictable. In user-facing RAG, predictability usually matters more than peak QPS.

There is a tradeoff. You give up some of the elegance of "one table, one query, let the engine sort it out." In return, you get control over memory locality, cardinality, and failure modes.

A few implementation truths matter here.

Tenant partitioning

In multi-tenant RAG, tenant_id is often treated as application metadata. That is a mistake. If every query has tenant_id = ?, then tenant is part of the retrieval topology. It should drive partitioning, routing, or index layout.

When teams leave tenant as a normal indexed column beside embeddings, they create the exact failure mode described in the OceanBase question: the engine has a vector index and a B-tree index, but no cheap way to apply both in one plan. Once cost estimates wobble, a full scan becomes a surprise to no one.

ACL filters

ACLs are often many-to-many, dynamic, and high-cardinality. If you evaluate them inline during ANN over a global corpus, plan shape becomes unstable.

The better pattern is to project ACLs into a smaller search domain ahead of time:

• per-tenant collections
• per-security-domain partitions
• materialized access groups
• candidate ID lists generated from an authorization index

This is less glamorous than pure vector-search design. It is also how systems stay upright.

Bounded post-filter fallout

ANN indexes assume the candidate pool contains enough relevant items that approximate probing can find them cheaply. If a strict metadata filter removes almost all ANN candidates after retrieval, you get two bad outcomes:

• latency spikes because the engine must overfetch aggressively, or
• recall drops because the system returns too few in-filter neighbors.

That is why post-filter-only designs look fine in low-selectivity test data and then fail in production. The fix is not just "increase top_k." Overfetching drives costs up fast, and recall still gets erratic for narrow slices.

Ingestion reliability

The scientific PDF embedding report is a reminder that ingestion failures are not a separate concern. If parsing or embedding fails for part of the corpus, teams often compensate by broadening candidate search, chunking more aggressively, or leaning harder on text-heavy retrieval. That increases corpus size, increases index fan-out, and makes hybrid filtering more expensive. Retrieval performance and content normalization belong in the same operating review.

Architecture / Implementation Guidance

Here is the architecture I would ship for a production enterprise RAG system where hybrid vector plus filters is already a bottleneck.

1) Physically segment the corpus by mandatory predicates

At minimum, partition by tenant_id. If tenants are huge, add a second segmentation dimension that is operationally stable, such as region, business_unit, or security_domain.

The point is not academic partition pruning. The point is to stop global ANN from even being considered.

A practical rule:

• If more than 95% of queries include a predicate, that predicate belongs in routing or partitioning.
• If the predicate also has compliance meaning, treat that as a hard requirement, not a tuning idea.

This can be implemented as:

• one physical table per tenant for large tenants
• shared table with partition pruning for small tenants
• one vector collection per tenant or per security domain in a dedicated vector engine
• a broker layer that routes to the correct shard before ANN executes

2) Use filter-first candidate generation

When a predicate is selective and indexable, generate candidate IDs first. Then run vector scoring only on that bounded set.

Typical cases:

• document_id IN (...)
• source_system = 'policy'
• language = 'de'
• version_status = 'current'
• ACL expansion to a doc or chunk ID set

This can be done with a sidecar search service, a temporary table, a CTE if the engine executes it efficiently, or an application-level two-step query. Purists may dislike the extra round trip. In practice, a predictable 70 ms beats an elegant 2 s.

3) Keep chunks and documents separate in filter design

Many systems attach all metadata directly to chunk rows. That is convenient and often wrong.

Use a document-level metadata index for coarse eligibility, then map to chunk IDs for ANN. Document metadata changes less often, has lower cardinality than chunks, and is a better place to execute ACL and lifecycle filters.

A clean pattern is:

• documents: doc-level metadata, ACL group projection, lifecycle state
• chunks: chunk text, embedding, doc_id, chunk_id
• doc_chunk_map if needed for indirection or denormalization

Filter documents first. Search chunks second.

4) Overfetch with selectivity-aware policies

You still need overfetch because ANN is approximate and filtered sets can be sparse. The trick is to make overfetch depend on estimated filter selectivity, not a fixed multiplier.

For example:

• broad filter: overfetch 2x
• medium selectivity: overfetch 5x
• very narrow filter or ACL-constrained slice: skip global ANN and search only the candidate ID set exactly or with a small local ANN index

This is the part experienced developers tend to underestimate. Static top_k * 10 overfetch rules become accidental latency bombs.

5) Maintain cardinality statistics outside the optimizer if needed

Many SQL optimizers are bad at estimating the interaction between vector access paths and metadata filters. If the engine will not do the right thing, own the decision.

Keep lightweight stats such as:

• chunks per tenant
• docs per access group
• chunks per language per tenant
• fraction of active vs archived content

Use those stats in the query broker to choose a plan:

• shard-local ANN
• candidate-set ANN
• exact scan of a tiny filtered subset

Yes, this is manual planning. For high-value retrieval paths, that is often the right move.

6) Prepare for index staleness and ingestion skew

Filtered retrieval gets fragile when fresh content is not indexed uniformly. A narrow filter can route a query into a slice whose vector index is lagging, sparse, or malformed.

Watch for:

• tenants with recent ingest spikes
• parser failures on scientific or symbol-heavy PDFs
• chunks with null embeddings or fallback embeddings
• index build lag by partition

A common failure mode is that one tenant or one document class quietly degrades recall, then query plans compensate by overfetching more broadly. That looks like a performance issue but often starts as an ingestion issue.

Code Snippets

The exact syntax depends on the engine, so the examples here show the retrieval pattern rather than pretending every database supports the same vector/filter pushdown semantics.

Pattern 1: document filter first, vector search second

-- Step 1: resolve the eligible document set using ordinary indexes
WITH eligible_docs AS (
  SELECT d.doc_id
  FROM documents d
  WHERE d.tenant_id = ?
    AND d.security_domain = ?
    AND d.version_status = 'current'
    AND d.language = ?
)
SELECT c.chunk_id, c.doc_id, c.embedding
FROM chunks c
JOIN eligible_docs e ON e.doc_id = c.doc_id;

That first step is often worth materializing into a temp table if the result set is reused across several candidate retrievals.

Then do vector search only on the eligible chunk subset. In engines that cannot constrain ANN to an ID set directly, push this logic into the application tier or a retrieval service.

from typing import List, Tuple

def retrieve(query_vec, tenant_id, security_domain, language, top_k=20):
    # 1) Metadata-first candidate generation
    eligible_doc_ids = sql.fetch_all(
        """
        SELECT doc_id
        FROM documents
        WHERE tenant_id = %s
          AND security_domain = %s
          AND version_status = 'current'
          AND language = %s
        """,
        [tenant_id, security_domain, language],
    )

    if not eligible_doc_ids:
        return []

    # 2) Bound the universe before vector scoring
    eligible_chunk_ids = sql.fetch_all(
        """
        SELECT chunk_id
        FROM chunks
        WHERE tenant_id = %s
          AND doc_id = ANY(%s)
        """,
        [tenant_id, eligible_doc_ids],
    )

    # Small candidate set: exact scoring can be cheaper and safer than ANN.
    if len(eligible_chunk_ids) <= 5000:
        return vector_store.exact_topk(
            query_vector=query_vec,
            chunk_ids=eligible_chunk_ids,
            top_k=top_k,
        )

    # Larger candidate set: use shard-local ANN with explicit filtering.
    overfetch = min(top_k * 5, 500)
    ann_hits = vector_store.ann_topk(
        query_vector=query_vec,
        tenant_scope=tenant_id,
        candidate_chunk_ids=eligible_chunk_ids,
        top_k=overfetch,
    )

    # Final exact rerank over the fetched set.
    return vector_store.rerank_exact(query_vec, ann_hits, top_k=top_k)

The useful detail here is the branch for small candidate sets. Many teams keep using ANN even when a filtered subset is tiny. That is wasted complexity and often worse for recall.

Pattern 2: route to partition-local indexes

class RetrievalPlanner:
    def choose_plan(self, tenant_stats, filter_stats):
        if tenant_stats.chunk_count < 200_000:
            return "tenant_local_ann"
        if filter_stats.estimated_chunks < 10_000:
            return "filtered_exact"
        if filter_stats.security_domain_selectivity < 0.01:
            return "domain_partition_ann"
        return "tenant_partition_ann"

This is not fancy. It is the sort of boring control-plane logic that saves systems from pathological plans.

Pattern 3: schema separation for coarse filtering

CREATE TABLE documents (
  doc_id BIGINT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  security_domain VARCHAR(64) NOT NULL,
  version_status VARCHAR(16) NOT NULL,
  language VARCHAR(8) NOT NULL,
  acl_group_id BIGINT NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  INDEX idx_docs_filter (tenant_id, security_domain, version_status, language, acl_group_id)
);

CREATE TABLE chunks (
  chunk_id BIGINT PRIMARY KEY,
  doc_id BIGINT NOT NULL,
  tenant_id BIGINT NOT NULL,
  embedding VECTOR(1536) NOT NULL,
  chunk_text TEXT NOT NULL,
  INDEX idx_chunks_doc (tenant_id, doc_id)
  -- plus vector index in the engine-specific form
);

The point is not the exact DDL. The point is to stop forcing every filter decision to happen on the chunk table itself.

Key Takeaways

The fix for hybrid ANN plus filter performance is usually architectural, not cosmetic. If full table scans are showing up in production RAG, the system is telling you the filter boundary is in the wrong place.

A few conclusions are worth keeping in view:

• Tenant and security scope should drive physical retrieval layout.
• Selective metadata predicates belong before ANN, not after it.
• Exact scoring on a small filtered subset is often better than ANN on a large global one.
• ACLs should be projected into a search-friendly form instead of evaluated ad hoc inside the vector path.
• Ingestion reliability matters because sparse or broken embeddings distort both recall and query cost.

The important insight is that hybrid retrieval is a query-planner problem hidden inside an embedding problem. Teams that treat it that way tend to recover predictable latency without giving up relevance. Teams that keep raising overfetch multipliers usually end up paying for full scans more slowly instead of avoiding them.

References / Notes

• Stack Overflow discussion of OceanBase 4.3.x ANN plus WHERE queries forcing full table scans
• Stack Overflow discussion of embedding failures on scientific PDFs