AI-narrated article. Prose is preserved with pronunciation adjustments; code and image cues refer to the written article.
Exact or Approximate Vector Search? Start With the Eligible Set
This is the AI-narrated article. The prose follows the written post; pronunciation is adjusted, and code and images are announced separately.
Choose and verify vector-search paths using the same tenant filter, embedding metric and snapshot, with a complete pgvector SQL walkthrough and recall contract.
TL;DR: Exact and approximate nearest-neighbour search must share the same eligibility predicate, distance metric and data snapshot before their results can be compared. This article provides a PostgreSQL and pgvector SQL walkthrough that exposes a limit-before-filter failure, creates an exact eligible baseline and inspects a filtered HNSW query. Iterative scans can recover more eligible candidates, but strict distance ordering does not guarantee exact recall. Report overlap, returned count and eligibility violations separately, and verify the executed plan before calling a result approximate. The example is statically reviewed and makes no latency or measured recall claim.
The nearest document must also be eligible
A retrieval service asks for the three closest approved documents belonging to one tenant. Its global vector index quickly finds three nearby documents, but all belong to another tenant. Filtering that shortlist produces an empty answer even though eligible documents exist. Increasing the requested result count may disguise the problem without fixing the contract.
For platform engineers, the decision is not simply exact search versus a faster index. The service needs an eligible result set, an explicit distance metric and an acceptable retrieval-quality target. PostgreSQL's planner then chooses an execution path. Adding an approximate index does not prove a particular query used it.
Two paths through the same eligibility contract
Exact search evaluates distances over every eligible vector, then selects the nearest results. An approximate nearest-neighbour index searches a candidate structure that can miss eligible neighbours. With pgvector HNSW, SQL filtering applies to rows returned by the index scan; a selective predicate can leave too few survivors.
Image. Exact search ranks the eligible set; approximate search filters candidates and may need further exploration
Both paths must preserve tenant and approval restrictions. Approximation may reduce completeness, but it must not relax eligibility. The diagram describes query flow, not measured recall or a security boundary.
A B-tree index on tenant and approval can narrow the exact workload before distance ranking. That can be attractive when a tenant has a small eligible population, even if the global table is large. An ANN path can make sense for broader populations. The PostgreSQL ordering documentation explains why the planner weighs index access against scanning and sorting. There is no universal row-count threshold that decides the better path.
Pin the metric and extension, not a promised plan
This example targets PostgreSQL 17 with pgvector 0.8.7 installed in the disposable database's public schema. It requires a database owner able to create a schema, table and indexes, plus psql. The release changelog verifies this extension version; the pinned README documents installation and the settings used below.
Provision that prerequisite through your normal local extension installation process. The script checks the installed version rather than upgrading an existing extension. Use a dedicated disposable database; do not point this example at a production connection.
The distance is Euclidean L2 throughout: <-> in queries and vector_l2_ops in the HNSW index. Switching to cosine requires matching query and operator class, not merely renaming a metric label. All fixture vectors are three-dimensional. Replace the column dimension and input consistently for your model; keep one embedding model and preprocessing contract within the comparison.
Complete SQL walkthrough
Save the following as topic-21-example.sql. It creates eight explicitly illustrative rows, demonstrates a misplaced filter, records an exact baseline, adds HNSW and compares the same filtered query. This is a functional query-design fixture, not a representative embedding workload or a benchmark.
Code example. Refer to the written article for the complete code.
Run with an explicitly chosen disposable database connection:
Code example. Refer to the written article for the complete code.
Set LAB_DATABASE_URL locally through your normal credential handling; do not paste credentials into article output or logs. The psql manual documents file execution and ON_ERROR_STOP. If a statement fails, the client exits; disconnecting rolls back the open transaction. A successful run reaches ROLLBACK, removing the example schema, table, indexes and temporary results while retaining the preinstalled extension.
The exact baseline materializes eligible rows before ordering by distance. PostgreSQL's CTE documentation explains this materialization barrier. The inner query has no nearest-neighbour ordering for HNSW to serve; the outer query ranks the materialized rows. Ordinary indexes may still find the eligible rows.
Diagnose empty results before tuning the graph
The deliberately wrong query limits the global shortlist before applying tenant and approval predicates. By construction, its three closest rows belong to tenant b, so it discards them all. The exact eligible shortlist instead contains IDs 5, 6 and 7. These follow from the supplied fixture, not from an executed database receipt.
Recovery starts with moving eligibility into the SQL query that orders and limits results. Do not fetch global candidates into an application and accidentally return one before checking access. In production, tenant identity must come from authenticated context; server-side row security policies can supply additional enforcement. Table owners and privileged roles can bypass row security, so validate policies using the application's actual database role. The fixture's tenant column is not a complete authorization implementation.
Correct SQL can still return fewer than the requested number under an ANN plan. Enable hnsw.iterative_scan = strict_order to continue exploration, subject to scan and memory limits. Strict ordering sorts the returned neighbours by distance; it does not make the result globally exact. hnsw.max_scan_tuples is an approximate scan limit, not a promise that every eligible neighbour will be found. These distinctions are documented in the pgvector filtering and iterative-scan guidance.
Choose recovery by the contract: allow a smaller result set, increase a bounded exploration budget, or route to an exact fallback. Returning extra ineligible rows is never a recall optimization.
Verify the path before reporting ANN recall
The script uses plain EXPLAIN, which shows the planned path without collecting runtime observations. A tiny fixture may produce an ordinary scan and sort even after HNSW exists. That is a valid exact execution path; comparing it with the baseline is not evidence about ANN quality.
Inspect the vector query's plan for documents_hnsw_idx. If it is absent, label the path exact. The EXPLAIN documentation distinguishes estimated plans from EXPLAIN ANALYZE, which actually runs the query. Collect runtime data only against an approved representative workload, and record the executed plan rather than assuming it matches an earlier estimate.
The comparison uses one repeatable-read transaction so both paths see the same data snapshot, including the example's own writes. PostgreSQL's isolation documentation explains that contract. A production evaluation must also align model version, query vector, eligibility predicate and distance function; comparing different snapshots or tenants measures a different problem.
Count missing neighbours and invalid results separately
Let the exact eligible top set contain min(k, eligible_count) IDs. Recall is the overlap divided by that exact-set size. If no eligible rows exist, recall is undefined; the SQL reports NULL rather than manufacturing a perfect score. Report returned count separately: a full response can still contain the wrong neighbours.
Eligibility violations are a different metric and must remain zero. Distance ties need an agreed policy: deterministic ID ordering, or an evaluation rule that accepts equivalent neighbours at the cutoff. This fixture has no cutoff ties; its exact query uses ID as a secondary key.
For realistic evaluation, retain per-query counts and cohort labels such as tenant size and predicate selectivity. Track latency distributions, result shortfall, overlap and timeouts together. Run enough representative queries to expose restrictive cohorts rather than reporting one aggregate. Refresh planner statistics with ANALYZE when testing changed data distributions. No latency, recall percentage or index-size measurement is claimed here: the SQL and sources received static review only.
Frequently asked questions
Does strict-order HNSW give exact nearest neighbours? No. It orders the returned candidates by distance; missed neighbours can still reduce recall.
Can exact search be preferable on a large table? Yes, when the eligible subset is small enough. Check the selected plan and representative workload instead of deciding from global size alone.
Should I divide recall by the requested k when fewer eligible rows exist? Use the exact eligible top-set size, and separately report the eligible count and result shortfall. Treat an empty denominator as undefined.
Does creating HNSW guarantee the query uses it? No. The planner can select another path. Verify the plan before describing a result as an ANN evaluation.