skip to content

In pgvector, what do the <->, <=> and <#> operators compute, and why must an index's operator class match the operator?

level: juniorimportance: must knowfreq 62%

answer

  1. three metrics, three operators
  2. smaller always means closer
  3. negative inner product, ascending scans
  4. one operator class per index

basics

~20 s

pgvector's <-> is L2 distance, <=> cosine distance and <#> negative inner product. An HNSW or IVFFlat index is built with one operator class, such as vector_cosine_ops, and serves only queries ordering by that class's operator.

solid answer

~40 s

pgvector's distance operators all return a number where **smaller means closer**: `<->` is Euclidean (L2) distance, `<=>` is cosine distance (1 minus cosine similarity), `<#>` is the **negative** inner product and `<+>` is L1 distance; on `bit` vectors `<~>` is Hamming and `<%>` Jaccard distance. `<#>` is negated because PostgreSQL can return index-ordered rows only in ascending order of an operator, so a larger dot product must sort first as a smaller number. Every HNSW or IVFFlat index is created with one operator class - `vector_l2_ops`, `vector_cosine_ops`, `vector_ip_ops`, `vector_l1_ops` - and the planner uses it only for `ORDER BY embedding <op> q` with that class's operator. HNSW has no default class, so you must name one; IVFFlat defaults to `vector_l2_ops`, which a cosine query will simply not use.

code

sql · 18 lines
sql
CREATE TABLE support_tickets (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id bigint NOT NULL,
    subject text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    embedding vector(1536)
);

CREATE INDEX support_tickets_embedding_cos_idx
    ON support_tickets USING hnsw (embedding vector_cosine_ops);

-- tickets most similar to ticket 42, nearest first
SELECT id, subject,
       1 - (embedding <=> (SELECT embedding FROM support_tickets WHERE id = 42)) AS similarity
FROM support_tickets
WHERE id <> 42
ORDER BY embedding <=> (SELECT embedding FROM support_tickets WHERE id = 42)
LIMIT 10;

go deeper

for a junior

Recall the three core operators and the metric each computes, and that smaller always means closer, which is why <#> is negative.

for a middle

Explain operator classes: an index serves one operator, HNSW has no default class, and ordering by a derived score instead of the raw operator loses the index.

for a senior

Show you would audit query shapes and index classes together, catching an IVFFlat index built with the default L2 class behind cosine queries.

for a principal

Frame the metric as a contract with the embedding model: changing models or normalisation means rechecking operator, class and index together.

## What pgvector adds to PostgreSQL **pgvector** is an extension that adds vector data types, distance operators and two approximate index access methods, `hnsw` and `ivfflat`. Every distance operator follows one convention: it returns a `double precision` value where **smaller means closer**. A nearest-neighbour query is therefore always written as `ORDER BY embedding <op> query_vector LIMIT k`, in ascending order. ## The operators | Operator | Computes | Works on | HNSW operator class | IVFFlat operator class | |---|---|---|---|---| | `<->` | L2 (Euclidean) distance | `vector`, `halfvec`, `sparsevec` | `vector_l2_ops` | `vector_l2_ops` (the default) | | `<=>` | cosine distance | `vector`, `halfvec`, `sparsevec` | `vector_cosine_ops` | `vector_cosine_ops` | | `<#>` | negative inner product | `vector`, `halfvec`, `sparsevec` | `vector_ip_ops` | `vector_ip_ops` | | `<+>` | L1 (taxicab) distance | `vector`, `halfvec`, `sparsevec` | `vector_l1_ops` | none | | `<~>` | Hamming distance | `bit` | `bit_hamming_ops` | `bit_hamming_ops` | | `<%>` | Jaccard distance | `bit` | `bit_jaccard_ops` | none | The `halfvec` and `sparsevec` classes follow the same naming (`halfvec_cosine_ops`, `sparsevec_l2_ops` and so on), and `sparsevec` can be indexed only with HNSW. Each metric also has a function form - `l2_distance()`, `cosine_distance()`, `inner_product()`, `l1_distance()` - which is handy in a select list but is not an ordering an index can serve. ## Why `<#>` is negated - PostgreSQL can use an index to return rows in the order of an operator only in **ascending** order of that operator; pgvector's documentation gives this as the reason for the sign. - For inner product, a **larger** value means more similar - the wrong direction for an ascending scan. - So `<#>` returns the inner product multiplied by -1: the most similar vector has the most negative value and sorts first. - To show the real inner product, multiply back in the select list: `(embedding <#> q) * -1`. Cosine works the same way: `<=>` is cosine **distance**, so cosine **similarity** is `1 - (embedding <=> q)`. ## Turning distances into scores without losing the index The planner matches a vector index only when the `ORDER BY` is the bare operator from the index's operator class, ascending, with a `LIMIT`. So: 1. Order by the raw operator: `ORDER BY embedding <=> q`. 2. Compute the human-friendly score separately in the select list: `1 - (embedding <=> q) AS similarity`. 3. Never order by the derived score: `ORDER BY 1 - (embedding <=> q) DESC` returns the same rows on an exact scan, but it cannot use the index and forces a full scan and sort. ## Operator classes bind an index to one metric An HNSW or IVFFlat index organises vectors according to one distance function, chosen through its **operator class**: - An index built with `vector_l2_ops` serves only `<->`; a query ordering by `<=>` on that column cannot use it and runs as an exact scan. - If the application queries with two metrics, it needs two indexes - pgvector's documentation says to add an index for each distance function you want to use. - HNSW has **no default operator class** for `vector`, so `CREATE INDEX ... USING hnsw (embedding)` fails with PostgreSQL's error that data type vector has no default operator class for access method "hnsw". - IVFFlat **does** have a default, `vector_l2_ops`, so forgetting the class there quietly produces an L2 index that cosine queries ignore. ## Choosing the operator Use the metric the embedding model was trained for; its documentation says which. If the vectors are normalised to length 1, pgvector's documentation recommends inner product for the best performance. Why cosine, dot product and L2 produce the same ranking on unit vectors is general vector-search theory rather than a pgvector question. Two edge cases matter in practice: - `NULL` vectors are never indexed. - For cosine distance, zero vectors are not indexed either, because a zero-length vector has no direction. Rows like these never appear in approximate results, even though an exact scan would still consider them. In a support-ticket table this is how tickets whose embedding job failed silently drop out of similarity search once the index is in place.

  • Why does ORDER BY 1 - (embedding <=> q) DESC not use a vector_cosine_ops index?
    A pgvector index can only return rows in ascending order of the bare operator in its operator class. An expression such as `1 - (embedding <=> q)`, or any `DESC` ordering, is not that operator, so the planner computes every distance and sorts. Order by `embedding <=> q` ascending and put the similarity score in the select list instead.
  • What happens if you create a pgvector HNSW index without naming an operator class?
    `CREATE INDEX` fails: pgvector defines no default HNSW operator class for `vector`, so PostgreSQL reports that the type has no default operator class for access method "hnsw". IVFFlat behaves differently - its default is `vector_l2_ops` - so a forgotten class there silently builds an L2 index that cosine queries never use.
  • Which rows does a pgvector cosine index leave out?
    `NULL` vectors are never indexed, and for cosine distance zero vectors are not indexed either. Those rows vanish from approximate results even though an exact scan would still consider them.

  • <#> returns the inner product, so order by it DESC to find the nearest rows
  • <=> returns cosine similarity, so a higher value means more similar
  • One HNSW index on a column serves L2, cosine and inner-product queries alike
  • Leaving out the operator class on an IVFFlat index gives a cosine index
  • Ordering by 1 - (embedding <=> q) DESC still uses the cosine index