What operator classes must you use when creating pgvector indexes and why do they matter?

When creating an HNSW or IVFFlat index on a vector column, you must specify an operator class that tells PostgreSQL which distance metric the index is optimised for. The operator class in the index must match the distance operator used in queries, otherwise the planner cannot use the index.

-- CORRECT: operator class matches the query operator
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
SELECT * FROM items ORDER BY embedding <=> '[...]' LIMIT 5;
-- ^ PostgreSQL uses the index above

-- WRONG: mismatched operator class and query operator
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
SELECT * FROM items ORDER BY embedding <-> '[...]' LIMIT 5;
-- ^ PostgreSQL CANNOT use the index (different metric)
-- This falls back to a slow sequential scan!

-- Multiple indexes for different metrics on the same column:
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
-- Now queries using either <-> or <=> can use an index

-- Check index usage with EXPLAIN:
EXPLAIN SELECT * FROM items ORDER BY embedding <=> '[1,2,3]' LIMIT 5;
-- Should show: Index Scan using items_embedding_idx

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.