Without an Index: The Exact Sequential Scan

Exact, simple, and a baseline

With no vector index, ORDER BY embedding <-> :q LIMIT 10 makes PostgreSQL compute the distance to every row and sort: a sequential scan. That is exact (perfect recall), needs no tuning, honours every filter, and is fine up to tens or hundreds of thousands of rows. Always start here, and keep it as the ground truth against which to judge an index. Use EXPLAIN to see what the planner does: the plan below shows Seq Scan plus Sort. We load 20,000 random 32-dimension vectors (seeded for repeatability) to experiment; random data is only for mechanics, and real embeddings cluster, so measure on your own data.

From sequential scan to HNSW

Without an index PostgreSQL scans every row; an HNSW index answers in milliseconds at a small recall cost you can tune.

Load 20,000 vectors, run

I ran this SQL on PostgreSQL 16 with the pgvector extension, version 0.8.6, in a Docker container. setseed makes the random vectors repeatable. The table now has 20,000 rows of 32 dimensions.

SET client_min_messages = warning;
SELECT setseed(0.42);
DROP TABLE IF EXISTS big;
CREATE TABLE big (id serial PRIMARY KEY, embedding vector(32));
INSERT INTO big (embedding)
SELECT (SELECT array_agg(random())::vector(32) FROM generate_series(1,32) WHERE g > 0)
FROM generate_series(1, 20000) AS g;
SELECT count(*) AS rows, vector_dims(embedding) AS dims FROM big GROUP BY 2;

Output:

 setseed 
---------
 
(1 row)

 rows  | dims 
-------+------
 20000 |   32
(1 row)

The plan without an index, run

I ran this SQL on PostgreSQL 16 with the pgvector extension, version 0.8.6, in a Docker container. With no vector index the planner reads the whole table (Seq Scan) and sorts by distance. Costs are hidden with COSTS OFF so the output stays stable.

EXPLAIN (COSTS OFF)
SELECT id FROM big ORDER BY embedding <-> (SELECT embedding FROM big WHERE id = 1) LIMIT 10;

Output:

                   QUERY PLAN                   
------------------------------------------------
 Limit
   InitPlan 1 (returns $0)
     ->  Index Scan using big_pkey on big big_1
           Index Cond: (id = 1)
   ->  Sort
         Sort Key: ((big.embedding <-> $0))
         ->  Seq Scan on big
(7 rows)

Small tables rarely need an index

Up to tens of thousands of rows, an exact scan can answer in milliseconds. An index adds build time and recall risk for little gain.

Quick check: Why keep the exact scan even after adding an index?

• Indexes cannot be used without it
• It is always faster than the index
• It provides ground truth to measure the index's recall
• It reduces storage