Reference, Tuning & FAQ

Complete SQL function reference, GUC configuration parameters, performance tuning, monitoring, and FAQ for pgvector.

SQL Function Reference

Vector Functions

Function	Return Type	Description
`l2_distance(a, b)`	`float8`	Euclidean (L2) distance between `a` and `b` (`vector`, `halfvec`, `sparsevec`)
`inner_product(a, b)`	`float8`	Inner (dot) product $\sum a_i b_i$ (`vector`, `halfvec`, `sparsevec`)
`cosine_distance(a, b)`	`float8`	Cosine distance $1 - \frac{a \cdot b}{\\|a\\|\\|b\\|}$ (`vector`, `halfvec`, `sparsevec`)
`l1_distance(a, b)`	`float8`	Manhattan (L1) distance (\sum
`hamming_distance(a, b)`	`float8`	Hamming distance between two `bit` vectors
`jaccard_distance(a, b)`	`float8`	Jaccard distance between two `bit` vectors
`vector_dims(a)`	`integer`	Number of dimensions in `a` (`vector`, `halfvec`, `sparsevec`)
`vector_norm(a)`	`float8`	Euclidean norm $\\|a\\|_2$ (`vector`, `halfvec`, `sparsevec`)
`l2_normalize(a)`	same as input	Normalize vector `a` to unit L2 length (`vector`, `halfvec`, `sparsevec`)
`binary_quantize(a)`	`bit`	Quantize each element of `vector` or `halfvec` to `1` ( $> 0$ ) or `0` ( $\le 0$ )
`subvector(a, start, count)`	same as input	Extract `count` dimensions starting at 1-based index `start` (`vector`, `halfvec`)

Element-Wise Arithmetic Operators

a + b: Element-wise addition (vector, halfvec)
a - b: Element-wise subtraction (vector, halfvec)
a * b: Element-wise multiplication (vector, halfvec)
a || b: Vector concatenation (vector, halfvec)

GUC Parameters

Parameter	Type / Allowed Values	Default	Description
`hnsw.ef_search`	`integer` (`1`–`1000`)	`40`	Size of the dynamic candidate list during HNSW search
`hnsw.iterative_scan`	`off`, `relaxed_order`, `strict_order`	`off`	Automatically scan more HNSW tuples when `WHERE` filters reduce results below `LIMIT`
`hnsw.max_scan_tuples`	`integer`	`20000`	Maximum tuples visited during an HNSW iterative scan
`hnsw.scan_mem_multiplier`	`real`	`1`	Multiple of `work_mem` used for HNSW iterative scan state
`ivfflat.probes`	`integer` (`1`–`lists`)	`1`	Number of inverted lists probed during an IVFFlat query
`ivfflat.iterative_scan`	`off`, `relaxed_order`	`off`	Automatically probe additional IVFFlat lists when `WHERE` filters reduce results below `LIMIT`
`ivfflat.max_probes`	`integer`	`32768`	Maximum lists probed during an IVFFlat iterative scan

Performance Tuning & Monitoring

1. Check Index Size

SELECT pg_size_pretty(pg_relation_size('items_embedding_idx'));

2. Inspect Query Execution Plans

Use EXPLAIN ANALYZE to confirm whether an index scan is chosen and measure execution latency:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

3. Monitor Slow Queries with `pg_stat_statements`

SELECT
    calls,
    round((total_plan_time + total_exec_time) / calls) AS avg_time_ms,
    round((total_plan_time + total_exec_time) / 60000) AS total_time_min,
    query
FROM pg_stat_statements
ORDER BY total_plan_time + total_exec_time DESC
LIMIT 20;

4. Measure Recall

Compare approximate index results against exact sequential scan results inside a transaction:

BEGIN;
SET LOCAL enable_indexscan = off;
-- Run exact query here to collect ground-truth IDs
COMMIT;

Frequently Asked Questions (FAQ)

Why isn’t my query using the vector index?

Missing ORDER BY ... LIMIT: Index scans require both an ORDER BY <operator> and a LIMIT clause.
Mismatched operator class: The query operator must match the index operator class (<-> requires vector_l2_ops, <#> requires vector_ip_ops, <=> requires vector_cosine_ops).
Small table cost estimation: On small tables, PostgreSQL’s planner may correctly determine that a sequential scan is cheaper than an index scan. To force index usage during testing, run SET LOCAL enable_seqscan = off;.

How do I index vectors with more than 2,000 dimensions?

pgvector HNSW and IVFFlat indexes cap vector columns at 2,000 dimensions because PostgreSQL index tuples cannot exceed 8 KB page limits. You have three options:

Install vchord: vchord’s vchordrq index supports up to 60,000 dimensions directly on vector and halfvec columns.
Half-precision expression indexing: Cast to halfvec ((embedding::halfvec(3072)) halfvec_l2_ops) to index up to 4,000 dimensions.
Binary quantization: Use (binary_quantize(embedding)::bit(N)) bit_hamming_ops to index up to 64,000 dimensions.

Can I store vectors with different dimensions in the same column?

Yes, declare the column without a dimension modifier (embedding vector), though you can only create an index on rows that share the same dimension via a partial expression index:

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector);
CREATE INDEX ON items USING hnsw ((embedding::vector(3)) vector_l2_ops) WHERE (vector_dims(embedding) = 3);

Do vectors contain `NULL` or `NaN` / `Infinity`?

Vectors cannot contain NULL, NaN, or Infinity elements. A whole column value can be NULL; note that NULL vectors are not indexed in IVFFlat and are sorted last by default unless NULLS FIRST is specified.