Domain 2 of 4 · Chapter 9 of 12

Optimize query latency and reduce pgvector compute

Unlock the complete study guide + 1,040 practice questions across 16 full exams.

Bundled into the existing Developing AI Cloud Solutions on Azure premium course — no separate purchase.

14-day money-back guarantee — no questions asked.

Included in this chapter:

  • Three moments: the column, the index, the query
  • Where a tuning value lives: transaction, session, server
  • Choosing the access method: hnsw, ivfflat, or diskann
  • Tuning hnsw: m, ef_construction, and ef_search
  • Tuning ivfflat: lists at build, probes per query
  • Shrinking the vector: precision, quantization, ceilings
  • Reading the plan: Index Scan or Seq Scan
  • Exam-pattern recognition

Choosing an approximate access method on Azure Database for PostgreSQL flexible server

Decision axishnswivfflatdiskann
Where it comes fromvector extension (pgvector)vector extension (pgvector)pg_diskann extension, documented as supported only on Azure Database for PostgreSQL flexible server
Build costSlower to build, uses more memoryFaster to build, uses less memorySlower to build and more memory than ivfflat
Speed-recall tradeoffBetter than ivfflatLower than hnswDocumented for high recall and low query latency at very large scale
Needs data before you buildNo training step, so it can be created on an empty tableYes: recall depends on building after representative rows are loadedNo training step, so it can be created on an empty table
Build-time optionsm (16 by default), ef_construction (64 by default)listsmax_neighbors (32 by default), l_value_ib, product_quantized (false by default)
Search-effort settinghnsw.ef_search (40 by default)ivfflat.probes (1 by default)diskann.l_value_is (100 by default)
Indexable dimensions for a vector columnUp to 2,000Up to 2,000Up to 16,000 from v0.6, with product quantization enabled

Decision tree

More than 2,000 dimensions to index?Corpus very large and memory-bound?Index built before rows are loaded?Build cost the binding constraint?diskann with product_quantized (v0.6+),or halfvec, bit, or fewer dimensionsdiskann: high recall, low latency at scalehnsw: no training step, builds on emptyivfflat: faster build, less memoryhnsw: better speed-recallyesyesyesyesnononono
Cheat sheet

Cheat sheet

  • HNSW gives high recall and stable low latency but is slower to build and memory-heavy
  • IVFFlat builds fast and is memory-light but must be built on representative data
  • Both indexes are approximate; without one, a similarity query does an exact sequential scan
  • pg_diskann as an Azure-specific ANN index
  • HNSW build parameters m and ef_construction cost build time and memory, and only a rebuild changes them
  • hnsw.ef_search is a runtime setting that trades recall against latency without a rebuild
  • IVFFlat lists is set at build; ivfflat.probes is tuned per query
  • On Azure Database for PostgreSQL flexible server a server parameter is read-only, dynamic, or static, and a static one needs a restart
  • Indexing embeddings as halfvec roughly halves the bytes per vector without changing the stored column
  • Binary quantization cuts memory furthest and is normally paired with a re-rank
  • HNSW and IVFFlat index at most 2000 dimensions for vector; a different representation raises the ceiling
  • EXPLAIN ANALYZE reveals whether the query uses the ANN index or falls back to a Seq Scan
  • An ANN index accelerates ORDER BY distance ... LIMIT k, not a bare distance predicate
  • Iterative index scans keep scanning the ANN index until enough filtered rows are found, up to a documented bound

Unlock with Premium — includes all practice exams and the complete study guide.

Also tested in

References

  1. Optimize performance when using pgvector in Azure Database for PostgreSQL flexible server
  2. PostgreSQL: CREATE INDEX (access methods and operator classes)
  3. List of extensions and modules by name in Azure Database for PostgreSQL flexible server
  4. PostgreSQL: SET (SESSION and LOCAL scope)
  5. Server parameters in Azure Database for PostgreSQL flexible server
  6. List read-only server parameters
  7. List read-write dynamic server parameters
  8. List read-write static server parameters
  9. Enable and use DiskANN in Azure Database for PostgreSQL flexible server
  10. Vector search in Azure Database for PostgreSQL flexible server (pgvector basics)
  11. PostgreSQL: resource-consumption parameters (maintenance_work_mem, max_parallel_maintenance_workers)