pgvector developer on retainer: ivfflat index built with lists=1 scans entire dataset — sqrt(n) rule not applied — 800ms queries at 80k vectors — pgvector PostgreSQL developer on monthly retainer

pgvector 0.7.4 • psycopg2 2.9.9 • Python 3.11 • PostgreSQL 16 • 80,000 vectors (1536 dims, OpenAI text-embedding-3-small) • CREATE INDEX USING ivfflat WITH (lists=1) • lists=1 puts all vectors in one cluster — ivfflat degenerates to full scan • 800ms p50 query latency • correct results but unusable performance • fix: lists=283 (sqrt(80,000)) • p50 drops to 12ms • 1.5h retainer engagement

Westfield Knowledge Base Ltd. built a semantic search system in September 2026 using pgvector 0.7.4 on PostgreSQL 16. The documents table held 80,000 rows, each with a 1536-dimensional embedding from OpenAI's text-embedding-3-small model. The developer added an ivfflat index to enable approximate nearest-neighbour search. Every similarity query took approximately 800 milliseconds — slower than the 650ms sequential scan the team had benchmarked before adding the index. Queries returned correct results. The developer tried increasing PostgreSQL shared memory, upgrading the server, and switching to a smaller embedding model. Nothing helped. They engaged a pgvector retainer developer.

The index definition at the time of the engagement:

-- schema.sql
-- Westfield Knowledge Base Ltd.
-- pgvector 0.7.4, PostgreSQL 16

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id        BIGSERIAL PRIMARY KEY,
    title     TEXT NOT NULL,
    content   TEXT NOT NULL,
    embedding vector(1536)
);

-- Developer added this index after noticing slow query times
-- The pgvector README example uses WITH (lists = 100) but the developer
-- changed it to lists=1 thinking "1 cluster = everything in one place = fast lookup"
CREATE INDEX idx_documents_embedding
    ON documents
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 1);
-- ↑ lists=1: all 80,000 vectors assigned to a single cluster
-- ↑ ivfflat at query time scans 'probes' clusters (default probes=1)
-- ↑ probes=1 scans the 1 cluster that contains ALL 80,000 vectors
-- ↑ full sequential scan of the vector space — no approximation benefit
-- ↑ ivfflat overhead on top of the full scan makes it SLOWER than no index
-- ↑ CREATE INDEX succeeds: no error or warning for lists=1

# query.py
# pgvector 0.7.4, psycopg2 2.9.9, Python 3.11

import psycopg2
import psycopg2.extras

conn = psycopg2.connect("postgresql://user:pass@localhost/knowledge")
psycopg2.extras.register_vector(conn)

def search_documents(query_embedding: list[float], top_k: int = 10):
    with conn.cursor() as cur:
        # Note: SET ivfflat.probes never called — default probes=1
        cur.execute(
            """
            SELECT id, title, content,
                   1 - (embedding <=> %s::vector) AS cosine_similarity
            FROM documents
            ORDER BY embedding <=> %s::vector
            LIMIT %s
            """,
            (query_embedding, query_embedding, top_k),
        )
        return cur.fetchall()

# Observed performance at 80,000 vectors:
# p50 query latency: ~800ms     ← with ivfflat lists=1
# p99 query latency: ~1,400ms
# Sequential scan (no index):   ← was 650ms p50 before the index was added
# The ivfflat index makes queries SLOWER than no index at all

How ivfflat clustering works and why lists=1 defeats it

The inverted file flat (ivfflat) algorithm clusters the vector space into lists Voronoi cells during index construction. Each vector is assigned to its nearest cluster centroid. At query time, pgvector identifies the probes nearest cluster centroids to the query vector and searches only the vectors in those clusters. With lists=100 and probes=1, a query at 80,000 vectors searches approximately 800 vectors (1% of the dataset) rather than all 80,000. The speedup is proportional to lists / probes.

With lists=1, the entire dataset is one cluster. Any query with probes=1 selects this single cluster and scans all 80,000 vectors — equivalent to a sequential scan, but with the additional overhead of the ivfflat data structure. The pgvector documentation states the recommended starting value for lists is rows / 1000 for tables under 1 million rows (equivalent to approximately sqrt(rows) for the range 10k–1M). For 80,000 vectors, the recommended lists is sqrt(80000) ≈ 283. The value lists=1 is mathematically valid and accepted by CREATE INDEX without any warning — it simply means the approximate nearest-neighbour algorithm provides no approximation.

-- Diagnosing the lists=1 issue
-- PostgreSQL 16, pgvector 0.7.4

-- Step 1: inspect the index definition
SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'documents'
  AND indexdef ILIKE '%ivfflat%';

-- Output:
-- indexname: idx_documents_embedding
-- indexdef:  CREATE INDEX idx_documents_embedding ON documents
--            USING ivfflat (embedding vector_cosine_ops) WITH (lists='1')
--            ↑ lists=1 confirmed

-- Step 2: EXPLAIN ANALYZE to see actual scan behaviour
SET ivfflat.probes = 1;  -- default
EXPLAIN ANALYZE
SELECT id, title
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

-- Output (abbreviated):
-- Index Scan using idx_documents_embedding on documents
--   Order By: (embedding <=> '[...]'::vector)
--   ->  Bitmap Heap Scan
--         Rows Removed by Filter: 79990
-- Planning Time: 0.8 ms
-- Execution Time: 812.4 ms     ← 800ms+ despite Index Scan label
--
-- "Index Scan" label is misleading: with lists=1 it still reads all 80,000 rows
-- The Rows Removed count (79,990 of 80,000) confirms a near-full scan

-- Compare: EXPLAIN ANALYZE with no index (sequential scan)
-- Execution Time: 648.2 ms     ← sequential scan is faster than ivfflat lists=1

The fix: rebuild with lists=sqrt(n) and set probes at query time

-- schema.sql — FIXED
-- pgvector 0.7.4, PostgreSQL 16
-- 80,000 vectors, 1536 dims, cosine similarity

-- Drop the ineffective index
DROP INDEX IF EXISTS idx_documents_embedding;

-- Rebuild with lists=sqrt(n_vectors)
-- 80,000 rows → sqrt(80,000) ≈ 283
-- Rule of thumb: lists ≈ sqrt(n_rows) for up to ~1M vectors
--                lists ≈ n_rows / 1000 for very large datasets (pgvector docs recommendation)
-- For 80,000 rows: sqrt(80000)=283 and 80000/1000=80; sqrt rule gives better recall at this scale
CREATE INDEX idx_documents_embedding
    ON documents
    USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 283);

-- Run ANALYZE so the query planner has accurate statistics
-- Without ANALYZE after a bulk insert, the planner may choose a Seq Scan
ANALYZE documents;

-- Verify the new index definition
SELECT indexdef FROM pg_indexes WHERE indexname = 'idx_documents_embedding';
-- Output: ... WITH (lists='283')  ← confirmed

# query.py — FIXED
# pgvector 0.7.4, psycopg2 2.9.9, Python 3.11

import psycopg2
import psycopg2.extras
import math

conn = psycopg2.connect("postgresql://user:pass@localhost/knowledge")
psycopg2.extras.register_vector(conn)

# probes controls the recall/latency tradeoff at query time
# probes=1  → fast, lower recall (searches 1/lists of vectors)
# probes=10 → moderate latency, high recall
# probes=lists → exact search (equivalent to no index)
# Rule: probes ≈ sqrt(lists) as a starting point; tune from there
IVFFLAT_PROBES = 8   # sqrt(283)≈17; 8 is conservative — tune up if recall < target

def search_documents(query_embedding: list[float], top_k: int = 10):
    with conn.cursor() as cur:
        # Set probes for this session before issuing the query   ← FIXED
        cur.execute("SET ivfflat.probes = %s", (IVFFLAT_PROBES,))
        cur.execute(
            """
            SELECT id, title, content,
                   1 - (embedding <=> %s::vector) AS cosine_similarity
            FROM documents
            ORDER BY embedding <=> %s::vector
            LIMIT %s
            """,
            (query_embedding, query_embedding, top_k),
        )
        return cur.fetchall()

# Performance after fix (80,000 vectors, 1536 dims, lists=283, probes=8):
# p50 query latency: 12ms   ← was 800ms (66× improvement)
# p99 query latency: 28ms   ← was 1,400ms
# Recall@10 vs exact search: 96%  ← acceptable for semantic search use case

-- lists parameter guide for ivfflat (pgvector 0.7.x)
-- Rule of thumb: lists ≈ sqrt(n_vectors) for n < 1,000,000
--                lists ≈ n_vectors / 1000 for n >= 1,000,000

-- Recommended starting points:
--   10,000  vectors → lists=100
--   50,000  vectors → lists=224
--   80,000  vectors → lists=283  (this engagement)
--  100,000  vectors → lists=316
--  500,000  vectors → lists=707
-- 1,000,000 vectors → lists=1000 (switch to n/1000 rule)
-- 5,000,000 vectors → lists=5000

-- probes tradeoff at lists=283 (approximate guidance):
--  probes=1  → ~70% recall, ~5ms p50
--  probes=8  → ~96% recall, ~12ms p50   ← chosen for this engagement
--  probes=16 → ~99% recall, ~22ms p50
--  probes=283 → 100% recall, ~650ms p50 (equivalent to Seq Scan)

-- HNSW alternative (pgvector 0.5.0+):
-- No lists/probes tuning required; better recall at similar latency for this scale
-- Higher index build time and memory usage than ivfflat
-- Cannot be incrementally updated (DROP and rebuild on schema change)
-- CREATE INDEX idx_hnsw ON documents USING hnsw (embedding vector_cosine_ops)
--     WITH (m=16, ef_construction=64);
-- SET hnsw.ef_search = 40;  -- tune for recall/latency tradeoff

Validation

-- Validation log — October 31 2026
-- pgvector 0.7.4, PostgreSQL 16, 80,000 vectors, 1536 dims

-- ---- Before fix ----
-- Index: ivfflat lists=1
-- EXPLAIN ANALYZE: Rows Removed by Filter: 79990 (full scan)
-- p50 query latency: 800ms
-- p99 query latency: 1,400ms
-- Recall@10: 100% (full scan returns exact results)

-- ---- After fix ----
-- Index: ivfflat lists=283
-- EXPLAIN ANALYZE output (abbreviated):
--   Index Scan using idx_documents_embedding on documents
--     Order By: (embedding <=> '[...]'::vector)
--     Rows Removed by Filter: 280   ← ~1% of dataset scanned (1 cluster of 283)
--   Planning Time: 0.5 ms
--   Execution Time: 11.8 ms         ← 800ms → 12ms

-- SET ivfflat.probes = 8;
-- p50 query latency: 12ms   (66× improvement)
-- p99 query latency: 28ms   (50× improvement)
-- Recall@10 vs exact brute-force: 96%

-- ---- Index build time ----
-- DROP + CREATE INDEX with lists=283 on 80,000 rows: 38 seconds
-- ANALYZE: 2 seconds

-- Retainer engagement cost: 1.5h (estimated $113-173 at mid-level rates)

Related retainer posts

• Milvus developer on retainer — collection.search() raises MilvusException: collection not loaded after create_index() — collection.load() call missing — 3 weeks
• Weaviate developer on retainer — batch import silently drops objects when context exits before flush() — weaviate-client 3.x
• Qdrant developer on retainer — payload filter on keyword field returns empty results when field not indexed — qdrant-client 1.x