Prompt or recipeOctober 10, 2026

pgvector: dedupe without killing the HNSW index

Kind: Code snippet / recipe
Language / model: SQL (PostgreSQL + pgvector)
Works with: pgvector HNSW and IVFFlat indexes
Gotchas: Check with EXPLAIN ANALYZE on the exact query your code sends: you want an Index Scan under a Limit.
hnsw.ef search must be at least the inner LIMIT.
If one document dominates the top 80, raise the over-fetch.

Short description

Get one result per document from a chunk table and still use the ANN index.

When to use it

Whenever you need DISTINCT ON or GROUP BY over a pgvector similarity search.

The prompt / code

-- DISTINCT ON above the ANN scan forces a sort by id and a distance for every row.
-- Do the indexed search first, over-fetch, then dedupe.
WITH ann AS (
  SELECT id, item_id, embedding <=> $1 AS dist
  FROM chunks
  ORDER BY embedding <=> $1
  LIMIT 80                -- ~4x the final k
), best AS (
  SELECT DISTINCT ON (item_id) item_id, id, dist
  FROM ann
  ORDER BY item_id, dist
)
SELECT * FROM best ORDER BY dist LIMIT 20;