Filtered Vector Search in pgvector: Combining WHERE Clauses with Similarity

2026-03-02

Filtered Vector Search in pgvector: Combining WHERE Clauses with Similarity

The Problem

Real applications almost never do pure similarity search. You want "find similar products in this category" or "nearest support tickets where status is open" or "most relevant documents owned by this tenant." The query looks natural enough — add a WHERE clause to your ORDER BY embedding distance — but the execution is more complicated than it appears.

A pgvector HNSW or IVFFlat index works by navigating a graph (or scanning inverted lists) to find the nearest neighbors globally. It does not know about your WHERE clause. The index returns the closest 100 vectors across the entire table, and then PostgreSQL filters out the rows that don't match your predicate. If your filter is selective — say, only 2% of rows have category_id = 7 — the index may need to scan thousands of candidates to find 10 that pass the filter. In the worst case, PostgreSQL falls back to a sequential scan because the index path is too expensive.

This creates three distinct failure modes:

Post-filter starvation: The index returns its ef_search candidates, the WHERE clause eliminates most of them, and you get fewer results than requested. A LIMIT 10 query returns 3 rows.
Sequential scan fallback: The planner decides the index is not selective enough for the combined query and chooses a sequential scan. On a million-row table, this means computing distances against every row.
Excessive index probing: The planner uses the index but must probe deep into the graph to find enough qualifying candidates. The query works but takes 50x longer than the unfiltered version.

The core tension is that vector indexes optimize for geometric proximity, while WHERE clauses optimize for attribute selectivity. Combining them requires strategies that aren't obvious from the syntax alone.

How to Detect It

The first step is identifying which vector queries have WHERE clauses and whether they're performing well:

-- Find vector queries with WHERE clauses (filtered search)
SELECT
    query,
    calls,
    mean_exec_time,
    rows
FROM pg_stat_statements
WHERE (query LIKE '%<=>%' OR query LIKE '%<->%')
  AND query LIKE '%WHERE%'
ORDER BY mean_exec_time DESC
LIMIT 10;

Look at the rows column relative to what you expect. If a LIMIT 10 query consistently returns fewer than 10 rows, you have post-filter starvation. If mean_exec_time is orders of magnitude higher than equivalent unfiltered queries, the planner is either falling back to a sequential scan or probing the index excessively.

Next, check whether your filter columns have supporting indexes:

-- Check if filtered columns have supporting indexes
SELECT
    t.relname AS table_name,
    i.indexname,
    i.indexdef
FROM pg_indexes i
JOIN pg_class t ON i.tablename = t.relname::text
WHERE i.indexdef LIKE '%vector%'
   OR i.indexdef LIKE '%btree%'
ORDER BY t.relname, i.indexname;

What you want to see: both a vector index (HNSW or IVFFlat) and a B-tree index on the column used in the WHERE clause. If you only have the vector index, PostgreSQL has no efficient way to evaluate the filter predicate.

Run EXPLAIN ANALYZE on a representative filtered query to see what the planner actually chose. A plan that shows Index Scan using hnsw_index followed by Filter: (category_id = 7) with a high Rows Removed by Filter count confirms the post-filtering problem.

How myDBA.dev Shows It

myDBA.dev's pgvector monitoring page shows filtered vector queries alongside their unfiltered equivalents, making the performance gap immediately visible. When a filtered similarity search takes 50ms while the same query without the WHERE clause takes 2ms, the comparison highlights that the filter is the problem, not the vector index itself.

The Queries page shows EXPLAIN plans for filtered vector queries, revealing whether PostgreSQL chose an index scan with post-filtering (high rows removed), a sequential scan fallback, or an efficient partial index scan. This tells you which fix strategy to apply.

How to Fix It

There are three approaches, each suited to different filter patterns:

Approach 1: Partial vector indexes for common filters

If you frequently filter on the same predicate (e.g., is_active = true, tenant_id = 5), create a partial HNSW index that only includes matching rows. The index is smaller, faster to search, and every candidate it returns already satisfies the filter.

-- Partial vector index for common filters
CREATE INDEX CONCURRENTLY idx_docs_active_hnsw
ON documents USING hnsw (embedding vector_cosine_ops)
WHERE is_active = true;

This is the most effective approach when the filter predicate is known at schema design time and covers a significant subset of queries. The downside: you need a separate index for each filter value, so it doesn't scale to high-cardinality columns like user_id.

Approach 2: Composite indexing with separate B-tree + vector indexes

For medium-cardinality filters (category, department, region), maintain both a B-tree index on the filter column and an HNSW index on the embedding. PostgreSQL can use a BitmapAnd to combine them, or the planner may choose to scan the B-tree first and then sort by vector distance.

-- Separate B-tree index for filter + vector index for sort
CREATE INDEX CONCURRENTLY idx_docs_category ON documents (category_id);
CREATE INDEX CONCURRENTLY idx_docs_hnsw
ON documents USING hnsw (embedding vector_cosine_ops);

Check EXPLAIN ANALYZE to verify the planner combines both indexes. If it ignores the B-tree index, you may need to increase work_mem or adjust random_page_cost to make the BitmapAnd path more attractive.

Approach 3: Pre-filter with CTE, then vector sort

When the filter is highly selective (returning a small fraction of the table), pre-filter into a CTE and sort by distance within the filtered set. This forces PostgreSQL to evaluate the WHERE clause first, then compute distances only on the surviving rows.

-- Pre-filter with CTE, then vector sort
WITH filtered AS (
    SELECT * FROM documents WHERE category_id = 5
)
SELECT * FROM filtered
ORDER BY embedding <=> $1
LIMIT 10;

The CTE approach trades index-accelerated vector search for a guaranteed-complete result set. It works well when the filtered set is small (thousands of rows), but degrades if the CTE materializes a large intermediate result. For filtered sets under ~10,000 rows, the sequential distance computation is fast enough that the index overhead isn't missed.

Choosing the right approach:

| Filter Pattern | Best Approach | Why | |---------------|---------------|-----| | Boolean flags (is_active, is_published) | Partial index | Small number of predicates, large row coverage | | Medium cardinality (category_id, region) | Dual indexes (B-tree + HNSW) | Planner can combine them | | High selectivity (returns <1% of rows) | CTE pre-filter | Filtered set is small enough for brute-force distance | | High cardinality (user_id, tenant_id) | Partial index per tenant (if few tenants) or CTE | Depends on tenant size distribution |

How to Prevent It

Design your filtered vector search strategy at schema creation time, not after performance degrades:

Identify your filter dimensions early. List every WHERE clause you'll combine with vector search. Design indexes for the most common combinations.
Benchmark filtered vs. unfiltered. Run both versions during development and set a performance ratio threshold — if filtered search is more than 5x slower than unfiltered, your indexing strategy needs work.
Increase hnsw.ef_search for filtered queries. A higher ef_search (e.g., 200 instead of the default 40) gives the index more candidates to return, increasing the chance that enough pass the filter. Set it per-session before filtered queries:

SET hnsw.ef_search = 200;

Monitor filtered vector query performance continuously. Filters that are unselective today may become highly selective as data grows or categories shift.

-- Monitor filtered vector query performance
SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
WHERE query ~* 'vector.*where|where.*order by.*<'
ORDER BY total_exec_time DESC
LIMIT 10;

Continuous monitoring with myDBA.dev surfaces filtered vector queries that have regressed — catching the problem when a new category makes an existing partial index insufficient, rather than when users report slow search results.