Hybrid Search Patterns with Postgres and pgvector

Most production vector queries are not simple nearest-neighbor searches. Rarely is the query to return "the 10 most similar documents in the entire table." Typically, it's something closer to: find the 10 most similar documents in the legal category, published in the last 30 days. That mix of similarity ranking plus scalar filters is hybrid search.

We have written before about HNSW indexes with pgvector and scaling vector data. Those posts go over indexes used to accelerate Nearest Neighbor queries with Approximate Nearest Neighbor indexes (ANN indexes). The next problem shows up the moment a WHERE clause is added. pgvector's iterative index scans help with filtered search, but they come with some tuning and tradeoffs.

When nearest neighbor meets a WHERE clause

Here is the query almost everyone writes first:

SELECT id, content
FROM docs
WHERE category = 'legal'
ORDER BY emb <=> '[0.031, ...]'
LIMIT 10;

It looks innocent. With a typical B-tree index, a WHERE plus ORDER BY is a solved problem: the planner picks an index, applies the filter, sorts what is left, and you move on.

A vector index finds nearby embeddings; a WHERE clause filters rows. Combining them forces Postgres to sacrifice either recall or performance.

The natural follow-up question is: why not just intersect the indexes? Postgres already knows how to combine two B-trees. Scan each index, build bitmaps of matching row IDs, BitmapAnd them together, and done. If you have an index on category and an index on emb, why can't the planner find the rows that are both legal and near the query vector the same way?

Because the two indexes are not answering the same kind of question.

A B-tree on category returns a set. The predicate category = 'legal' is a yes-or-no membership test. Every qualifying row ID goes into the set. That shape is perfect for intersection: set of legal rows, set of active rows, or tenant = 42, or (you get the point).

An HNSW (and IVFFlat) index returns an approximate ordered top-k, not a set. ORDER BY emb <=> query LIMIT 10 is not "rows that match." These indexes returns a ranked list. When returning values, the index walks a graph (or probes inverted lists), compares distances, and emits candidates ranked from nearest to farther and the more it returns, the longer the query time, more than RAM and CPU usage. With a LIMIT 10, you can just return 10, right? The ANN indexes do not include data about conditionals in the WHERE clause, so after returning the 10 closest, it then performs a check against WHERE conditionals. Some (or all) of those 10 may not pass the conditionals. Do you go back and find the next 10 closest? Do you quit? This is the tradeoff.

pgvector iterative index scans

Starting in pgvector 0.8, it implemented a solution to the problem of "top-k then hope the filter leaves enough rows." Iterative index scans keep walking the HNSW or IVFFlat (ANN index) until enough rows survive the WHERE clause, or until a safety limit says stop:

-- Continue scanning until enough filtered rows are found
SET hnsw.iterative_scan = relaxed_order;
-- or, if exact distance order matters more than recall/speed:
-- SET hnsw.iterative_scan = strict_order;
-- or ivfflat indexes:
-- SET ivfflat.iterative_scan = relaxed_order;

SET hnsw.max_scan_tuples = 20000;  -- cap how far the scan may expand
SET hnsw.ef_search = 40;

SELECT id, content
FROM docs
WHERE category = 'legal'
ORDER BY emb <=> '[0.031, ...]'
LIMIT 10;

With hnsw.iterative_scan equal to relaxed_order or strict_order, scan, filter, and if you are short of LIMIT, it go back to the ANN index for the next closest rows (with exact behavior dependant on graph navigation precision).

With the default (hnsw.iterative_scan = off), filtering is applied after a single index pass. If the condition matches about 10% of rows and hnsw.ef_search is 40, you should expect only about four survivors on average, even when you asked for LIMIT 10.

However, using iterative_scan is not free.

Tradeoffs to keep in mind:

• Low selectivity values: a rare conditional forces will continue to scan far more of the graph to collect 10 survivors than a common one.
• Setting with hard stop: hnsw.max_scan_tuples bounds how far iterative search will go, after which it will still return fewer than LIMIT rows.
• Strict vs relaxed ordering. strict_order prioritizes correct order over performance. relaxed_order allows results to be slightly out of order in exchange for performance.
• Memory usage If raising max_scan_tuples does not improve recall, pgvector's docs suggest raising hnsw.scan_mem_multiplier (a multiple of work_mem) so the scan can keep more state in memory.

So iterative scans are the right default for many hybrid queries, and when the ad hoc filters are still tightly controlled (not a choose-your-own-adventure predicate where users combine enough clauses to match almost nothing).

But perhaps, you want to control the situation a bit more.

Path 1: vector index first, then filter

This is what you get with iterative scans off, or what a single pass of the index is doing before it expands. Walk the HNSW graph, pull the nearest candidates you can find, then keep only the ones that pass category = 'legal':

-- Conceptual shape of the vector-first path (no iterative expansion)
SELECT id, content
FROM (
    SELECT id, content, category
    FROM docs
    ORDER BY emb <=> '[0.031, ...]'
    LIMIT 10
) nearest
WHERE category = 'legal';

This has predictable performance, because the graph traversal stays bounded. The tradeoff is recall. The top 10 nearest neighbors in the whole table are not the same as the top 10 nearest neighbors in the legal subset. If only 5% of your rows are legal, those top 10 overall might include zero legal documents, or maybe two. The filter did not fail; the candidate pool didn't include any survivors.

This failure mode is easy to miss in demos. On a development table with seed data where every category is roughly equal, it works just fine. On a production, multi-tenant system that allows users to define labels, it may not work so well.

Path 1a: change the application requirement

There is a less technical escape hatch: stop promising a fixed top-10. If the product can return however many rows survive the filter, then return as many as you have.

Path 2: scalar filter first, then rank by distance

Start from the other side. Use a B-tree on category, gather every matching row, then compute vector distance to pick the top 10:

-- Conceptual shape of the scalar-first path
SELECT id, content
FROM docs
WHERE category = 'legal'   -- narrow with a scalar index first
ORDER BY emb <=> '[0.031, ...]'
LIMIT 10;

With this, you take the full set, then rank it. The tradeoff is unpredictable query time. If it is millions of rows, you are computing distance across a huge set. With the matching set, the query will walk through the sequential-scan and performance a vector distance calculation for each value.

Workarounds and Tricks

Each use-case has the tradeoffs, and these workarounds will can help, but each one fits a particular situation:

• Always matching on the same low-cardinality values: (category, status, region, or limited number of set of tenants) can you bake the filter into a partial HNSW index?
• Need a full LIMIT not matter what: can you oversample in SQL, then filter and re-rank?
• Commonly re-run the same hybrid queries: can you cache the response so you are not re-querying on every request?

Partial HNSW index

Situation: the filter values are low-cardinality and stable, and you may consider creating a partial B-tree for the same predicate.

Build a conditional ANN index. The HNSW graph then contains only the filtered subset, so every node the index returns already satisfies the condition.

-- Build a partial index for the 'legal' category only
CREATE INDEX docs_legal_hnsw ON docs
    USING hnsw (emb vector_cosine_ops)
    WITH (m = 16, ef_construction = 64)
WHERE category = 'legal';

-- Query: PostgreSQL can use the partial index automatically
SET hnsw.ef_search = 40;

SELECT id, content
FROM docs
WHERE category = 'legal'
ORDER BY emb <=> '[0.031, ...]'
LIMIT 10;

The partial index is smaller (it only holds the filtered rows), builds faster, and has effectively 100% recall within that filtered subset.

The downside is maintenance: one index per value. That is manageable when you have a handful of categories or a few tenants. It is impractical when the filter is high-cardinality (user_id, free-form tags, or an arbitrary date range). If the predicate is open-ended, look at the next workarounds instead.

Oversample, then filter

Situation: a partial index is not practical, and iterative scans are off or hitting max_scan_tuples before they collect enough rows for a full LIMIT.

Ask the full-table vector index for a larger pool of candidates than you ultimately need:

-- No partial index needed: use the full-table HNSW
SET hnsw.ef_search = 40;

SELECT id, content
FROM (
    SELECT id, content, category, emb
    FROM docs
    ORDER BY emb <=> '[0.031, ...]'
    LIMIT 200          -- oversample: fetch 200 candidates
) candidates
WHERE category = 'legal'
ORDER BY emb <=> '[0.031, ...]'   -- re-rank the filtered subset
LIMIT 10;

The oversample factor (200 vs 10) needs to be large enough that the filtered pool contains your true top-10 among rows that match the filter. Think of it in terms of selectivity. If legal is about 10% of the table, a pool of 100 gives you a rough expectation of 10 legal documents, before you account for the fact that approximate search and uneven distribution will cheat you a bit. If selectivity is 0.1% of rows, you need a pool on the order of 10,000 to reliably recover 10 results, and at that point the post-filter approach is approaching "just scan a lot of the index anyway."

A back-of-the-napkin starting formula is:

pool_size = k / filter_selectivity × 2

For 10 results from a filter with 5% selectivity: 10 / 0.05 × 2 = 400. The extra × 2 is a cushion for approximation error and for filters that are not perfectly uniform across the embedding space. Measure on your data. If you routinely get fewer than k rows back, raise the pool. If the subquery is expensive and you always get plenty of survivors, lower it.

You do not have to hard-code that selectivity. Postgres already estimates it in pg_stats. For values that made the most-common list, use the stored frequency. For everything else, reuse the planner's own hack: assume the probability mass not in the MCV list is spread evenly across the remaining distinct values.

-- Estimate pool_size for category = 'legal', k = 10
WITH s AS (
    SELECT
        null_frac,
        n_distinct,
        coalesce(cardinality(most_common_freqs), 0) AS mcv_n,
        coalesce((SELECT sum(f) FROM unnest(most_common_freqs) AS f), 0) AS sum_common,
        most_common_vals::text::text[] AS mcv_vals,
        most_common_freqs AS mcv_freqs
    FROM pg_stats
    WHERE tablename = 'docs'
      AND attname = 'category'
),
mcv AS (
    SELECT val, freq
    FROM s,
         unnest(s.mcv_vals, s.mcv_freqs) AS u(val, freq)
    WHERE val = 'legal'
),
residual AS (
    -- Same idea as the planner when the value is absent from most_common_vals
    SELECT
        (1.0 - s.null_frac - s.sum_common)
          / greatest(
              CASE
                  WHEN s.n_distinct > 0 THEN s.n_distinct
                  ELSE (-s.n_distinct) * c.reltuples  -- negative n_distinct is a fraction
              END - s.mcv_n,
              1
            ) AS freq
    FROM s
    JOIN pg_class c ON c.relname = 'docs'
)
SELECT
    coalesce(m.freq, r.freq) AS filter_selectivity,
    ceil(10 / NULLIF(coalesce(m.freq, r.freq), 0) * 2) AS pool_size
FROM residual r
LEFT JOIN mcv m ON true;

If your filter labels keep missing most_common_vals and falling into that residual bucket, give ANALYZE a longer MCV list:

ALTER TABLE docs ALTER COLUMN category SET STATISTICS 1000;
ANALYZE docs;

The default is 100, so raising it stores more most-common values and frequencies, so more of your hybrid filters get a real selectivity instead of the even-split guess. Wire the estimate into the application (or a SQL function) before you set the oversample LIMIT, and the pool tracks the data as statistics refresh. For more on reading and shaping those catalogs, see Hacking the Postgres Statistics Tables for Faster Queries and Postgres Internals Hiding in Plain Sight.

One subtlety worth calling out: bump hnsw.ef_search when you oversample. The index has to explore enough of the graph to actually produce a large candidate set.

Cache the response

Situation: typically, the same users / pages / use-cases ask the same (or nearly the same) hybrid questions over and over: similar products, similar support articles, "docs like this one in legal," recommendations that do not change every minute.

We covered caching more broadly in Scaling Vector Data with Postgres. For hybrid search, two shapes show up constantly:

Pre-compute (store-time cache). When you insert or update a document, run the hybrid retrieval once and save the result IDs. Great for "similar to this row" features where the query vector is the row's own embedding and the filters are known ahead of time.

Post-query cache. When you cannot anticipate the query, run it on a cache miss, then store the result keyed by something stable: a hash of the query embedding (or source document id), the filter dimensions, and the limit. The next identical hybrid request becomes a primary-key lookup.

A lookup table in Postgres is enough for many apps:

CREATE TABLE hybrid_search_cache (
    cache_key   text PRIMARY KEY,  -- hash of query vector + filters + limit
    result_ids  bigint[] NOT NULL,
    scores      float8[],          -- optional: distances or other scores
    created_at  timestamptz NOT NULL DEFAULT now(),
    expires_at  timestamptz
);

-- On a hit: return cached IDs (join back to docs for display)
SELECT d.id, d.content
FROM hybrid_search_cache c
JOIN LATERAL unnest(c.result_ids) WITH ORDINALITY AS r(id, ord) ON true
JOIN docs d ON d.id = r.id
WHERE c.cache_key = $1
  AND (c.expires_at IS NULL OR c.expires_at > now())
ORDER BY r.ord;

-- On a miss: run the hybrid query, then:
INSERT INTO hybrid_search_cache (cache_key, result_ids, scores, expires_at)
VALUES ($1, $2, $3, now() + interval '1 day')
ON CONFLICT (cache_key) DO UPDATE
SET result_ids = EXCLUDED.result_ids,
    scores     = EXCLUDED.scores,
    created_at = now(),
    expires_at = EXCLUDED.expires_at;

Include the filter in the cache key. Caching "nearest 10" and later serving it for category = 'legal' is how you reintroduce Path 1's recall bug with extra steps. Tenant id, labels, date windows, and locale all belong in the key.

Invalidation is the real design work. Bust or recompute when source embeddings change, or when filter membership changes (a doc leaves legal). Short TTLs are a fine starting point if you are not sure; a learning loop on top of the cache (track clicks, dismissals, empty results) turns the table into something a bit smarter than a dumb key-value store.

Caching does not fix a bad hybrid plan. Get recall right for the filtered query first.

Diagnosing plan choice with EXPLAIN

Whatever pattern you pick, verify it. You may think you are using an approximate index, but the query may never actually hit them.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM docs
WHERE category = 'legal'
ORDER BY emb <=> '[0.031, ...]'
LIMIT 10;

Here is what the plan nodes are telling you:

If you see a Bitmap Heap Scan on the scalar column when you expected the vector index, the planner decided the scalar filter was selective enough to be cheaper that way.

And as we said back in the HNSW post: if the index is not being used, simplify the query until it is, then build complexity back in one piece at a time. Hybrid queries are exactly where people accidentally disable the index with an expression the planner cannot match.

Choosing a workaround

A short decision guide:

• Filtered vector search with tightly bound ad hoc predicates: start with pgvector iterative index scans (hnsw.iterative_scan), and tune max_scan_tuples / ordering mode for your selectivity.
• Stable, low-cardinality filter you query constantly: partial HNSW, often better than repeatedly expanding a full-table scan.
• Ad hoc or high-cardinality filter where iterative scans hit their ceiling: oversample then filter in SQL.
• Repeated hybrid questions with stable answers: cache responses (include filters in the cache key; invalidate when embeddings or membership change).

Hybrid search is not one query shape. It is a set of tradeoffs between recall, and performance. Postgres and pgvector give you the retrieval primitives; the workarounds above are how those primitives show up in production systems.