Filtered vector search with pgvector — how do you combine WHERE category = 'X' with ORDER BY embedding <=> $1?

Q: Filtered vector search with pgvector — how do you combine WHERE category = 'X' with ORDER BY embedding $1?

**Target Audience:** Developers running semantic search on PostgreSQL with pgvector, and candidates preparing for backend interviews **Difficulty:** Medium **Engines:** PostgreSQL with pgvector. pgvector is not installed on the verification machine, so the behaviour is shown with plain-SQL simulations that ran on sql.js 1.10.2 (SQLite) and a PostgreSQL 16.15 plan; pgvector SQL is marked "not run here" and its settings are cited from the [pgvector README](https://github.com/pgvector/pgvector). > **How to read this doc:** Every new term is explained in plain words first. When it is a term interviewers expect you to know, it is tagged `📌 Interview term:` so you can learn the vocabulary too. This page is the applied follow-up to [HNSW vs IVFFlat](/interview-question/pgvector-cosine-similarity-index-in-2025-26-hnsw-vs-ivfflat-for-semantic-search-), which explains embeddings, cosine distance and how the two index types work. Here the question is what happens when a vector search also has

The problem

Question presented to candidate: "Our help-centre search runs SELECT ... WHERE category = 'billing' ORDER BY embedding <=> $1 LIMIT 3 on pgvector with an HNSW index. Sometimes it returns one row or none, even though there are plenty of billing articles. Why, and how would you fix it?"

What a strong answer should cover:

Why it happens: the ANN index returns its best candidates first (about hnsw.ef_search, default 40), and the WHERE filter runs on those candidates afterwards, so a selective filter can leave fewer than LIMIT rows
Exact pre-filtering: a B-tree on category plus an exact distance sort is correct and fast when the filtered set is small; the planner can choose it by itself
Iterative index scans (pgvector 0.8.0): hnsw.iterative_scan = strict_order or relaxed_order keeps scanning until enough rows pass, bounded by hnsw.max_scan_tuples
Structural fixes: a partial HNSW index per hot value (WHERE category = 'billing'), or list partitioning by category with one index per partition
Trade-offs by selectivity, and how to check which plan you got with EXPLAIN

Clarifying questions expected:

How many distinct categories, and what fraction of rows does a typical filter keep?
Is the filter one fixed column, or arbitrary combinations (tenant plus language plus date)?

Code / implementation expected: Yes — show the failing pattern, the exact pre-filter, and the pgvector settings or index that fix it.

Schema used by the examples:

pgvector
filtering
ann

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers running semantic search on PostgreSQL with pgvector, and candidates preparing for backend interviews Difficulty: Medium Engines: PostgreSQL with pgvector. pgvector is not installed on the verification machine, so the behaviour is shown with plain-SQL simulations that ran on sql.js 1.10.2 (SQLite) and a PostgreSQL 16.15 plan; pgvector SQL is marked "not run here" and its settings are cited from the pgvector README.

How to read this doc: Every new term is explained in plain words first. When it is a term interviewers expect you to know, it is tagged 📌 Interview term: so you can learn the vocabulary too.

This page is the applied follow-up to HNSW vs IVFFlat, which explains embeddings, cosine distanc

The solution is hidden so you can try first

13 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLThe right answer: filter first, then rank the billing rows by exact cosine distance

SQLThe bug, simulated: take the index top 4 over all categories, then filter, and nothing is left

SQLWhat an iterative scan has to do: keep walking the index order until 3 billing rows have passed

PostgreSQL: with a B-tree on category the plan filters first and sorts only the matching rows

CREATE INDEX articles_category_idx ON articles (category);
ANALYZE articles;
SET enable_seqscan = off;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id
FROM articles
WHERE category = 'billing'
ORDER BY 1 - (e1 * 0.5 + e2 * 0.866)
LIMIT 3;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.