Postgres 19 query hints: the pgvector HNSW cheatsheet

Tuesday morning, 09:14. A client's customer-support RAG endpoint had drifted from 180ms p95 to 2.4 seconds overnight. No deploys, no schema change. The culprit was a single planner flip: Postgres had decided the HNSW index on the embedding column was no longer worth the seek, and was running a sequential scan over 3.1 million rows before applying the vector ORDER BY. The pg_hint_plan block was still in the SQL, but a tenant-rewrite layer had quoted the string. We could see the comment in the log. The parser couldn't.

That morning is why we spent the spring porting three production RAG pipelines off pg_hint_plan and onto the native query hints that landed in the Postgres 19 beta. The proposal popped up on Hacker News again last week, and the discussion mirrored ours: hints aren't a planner cure, they're a planner audit tool. Most teams will use four of them. The rest are situational.

This is the cheatsheet. Seventeen hint syntaxes, ranked by how often the Postgres 19 planner respects them when the underlying query touches a pgvector HNSW index with a metadata filter. Numbers come from our own three pipelines (legal, support, ops), not synthetic benches.

The seventeen hints, by group

Hints in Postgres 19 attach to a query as a block comment immediately after the verb. The syntax is deliberately close to pg_hint_plan so the migration is mostly mechanical:

SELECT /*+ INDEX(chunks chunks_embedding_hnsw) ROWS(chunks 50) */
  chunk_id, content, embedding <=> $1 AS distance
FROM chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 8;

Scan control

• SEQ_SCAN(table) forces a sequential scan.
• INDEX_SCAN(table, index) forces a specific b-tree or HNSW index.
• BITMAP_SCAN(table, index) forces a bitmap heap scan.
• NO_INDEX(table, index) excludes one index from consideration.
• INDEX_ONLY_SCAN(table, index) promotes an index-only path.

Join control

• HASH_JOIN(t1 t2)
• NESTED_LOOP(t1 t2)
• MERGE_JOIN(t1 t2)
• LEADING((t1 t2 t3)) fixes the join order.
• MEMOIZE(t1 t2) and NO_MEMOIZE(...) toggle the memoize node introduced in 14.

Cardinality and cost

• ROWS(table n) overrides the rowcount estimate at the node.
• CARD(table n) overrides the base relation cardinality. Almost always you want ROWS instead.

Execution shape

• PARALLEL(table workers) forces or caps parallel workers.
• NO_PARALLEL(table)
• MATERIALIZE(cte_name) forces a CTE to materialise instead of inline.
• JIT(on|off) local JIT toggle.
• SET_LOCAL(work_mem '256MB') statement-scoped GUC override.

What the planner actually respects under HNSW

This is the part that surprised us. Of those seventeen, only four are reliably honoured when the plan crosses a pgvector HNSW node. The rest get accepted, logged, and then quietly overruled by the cost model.

Tier 1: respected, always

INDEX_SCAN, NO_INDEX, SET_LOCAL, and ROWS. These four account for around 94% of the hint annotations across our three pipelines. INDEX_SCAN is the one you reach for when the planner falls off the HNSW path under a selective metadata filter (the Tuesday-morning story above). ROWS is the one that quietly does the most work, because pgvector's cost estimates for ORDER BY distance are still optimistic about how many heap fetches a recall@8 actually triggers.

Tier 2: respected, conditionally

PARALLEL, NO_PARALLEL, HASH_JOIN, NESTED_LOOP, LEADING, MATERIALIZE. These work when the underlying relations are large enough and the planner has not already pinned a path through row estimates. PARALLEL in particular is brittle under HNSW because the index scan itself is not currently parallel-safe in pgvector 0.9. The hint is accepted and the worker count silently capped at one for that branch.

Tier 3: cosmetic

MERGE_JOIN, BITMAP_SCAN, INDEX_ONLY_SCAN, MEMOIZE, NO_MEMOIZE, CARD, JIT. These do something in isolated cases but we have not seen one of them change a plan in production. CARD specifically is almost always the wrong tool. What you want is ROWS at the node above the filter, not a base-relation override.

The pgvector HNSW gotcha

The reason this matters at all: pgvector's HNSW index has two cost-model knobs (hnsw.ef_search and the index's internal m) and one major planner blind spot. The blind spot is filtered search. When you write WHERE tenant_id = $1 ORDER BY embedding <=> $2, the planner currently treats the HNSW scan as if recall and cost are independent of the selectivity of tenant_id. They are not. A tenant with 5,000 rows in a 3M-row table will force HNSW to walk far more nodes to fill the LIMIT than a tenant with 500,000 rows.

The pgvector changelog acknowledges this, and the iterative scan introduced in 0.8 helps, but the planner still does not know how aggressive to be. This is exactly where ROWS earns its keep:

-- Tell the planner the HNSW node will surface ~40 rows
-- after the tenant filter, not the 800k it currently estimates.
SELECT /*+ ROWS(chunks 40) INDEX_SCAN(chunks chunks_embedding_hnsw) */
  chunk_id, content
FROM chunks
WHERE tenant_id = $1
  AND deleted_at IS NULL
ORDER BY embedding <=> $2
LIMIT 8;

On the legal pipeline (3.1M chunks, 740 tenants, skewed Zipf), this single annotation cut p95 from 2.4s to 190ms and held there across three weeks of changing traffic shape.

What we ripped out of pg_hint_plan

The migration was less dramatic than feared. pg_hint_plan has been the production-grade hint extension since 9.6, and its syntax was the basis for the 19 patch, so most rewrites were a comment-block format change. Three categories of pain:

Quoted SQL. Any layer that quoted the SQL (string-builders, some ORMs, tenant rewriters) would mangle the /*+ ... */ block in pg_hint_plan because it required exact placement. Postgres 19 is more forgiving about whitespace inside the block but still strict about position. We solved this by moving hints into prepared-statement names where possible, and using a normalisation map at the connection-pool layer for the rest.

Application-level injection. A few pipelines built hints in the application layer based on tenant size. We replaced this with a planner-side function:

CREATE OR REPLACE FUNCTION rag.hint_for_tenant(t uuid)
RETURNS text LANGUAGE sql STABLE AS $$
  SELECT format('/*+ ROWS(chunks %s) INDEX_SCAN(chunks chunks_embedding_hnsw) */',
    GREATEST(20, LEAST(400, (chunk_count / 100)::int)))
  FROM rag.tenant_stats WHERE tenant_id = t;
$$;

The query layer now injects this once per request using a parameterised template, and the hint is keyed off live row counts instead of a guess.

Observability. pg_hint_plan logged accepted and rejected hints into its own channel. The 19 native version writes into pg_stat_statements's hint_used column and into auto_explain output. We had to rewrite our dashboards, but ended up with cleaner signal: a hint that's been silently ignored for three weeks now shows up as a divergence between hint_attempted and hint_used counters, surfaced in Grafana via a Postgres exporter query.

What we would do differently

Two things. First: we under-invested in cardinality observability before adding hints. The right order is to fix statistics (ANALYZE frequency, extended statistics on filter and vector column pairs) before you reach for a hint. We added extended stats on (tenant_id, deleted_at) and several of our Tier 2 hints became unnecessary.

Second: hints are documentation. Every production hint in our codebase has a one-line reason attached, and we audit them quarterly. Two of our original twelve hints were stale within four months because the underlying row distributions had shifted. A hint with no reason is a fossil that will eventually mislead a future engineer.

When we built the retrieval layer for one of our legal-tech clients, the recurring problem was that the planner's cost model was estimating filtered HNSW as if it were a b-tree. We ended up treating hints as a forcing function for fixing statistics, which is now how we ship every new AI agents and RAG retrieval pipeline.

If you have a RAG pipeline on pgvector today, the five-minute audit: run EXPLAIN (ANALYZE, BUFFERS) on your slowest tenant's worst query, look for a sequential scan on the embedding table, and check whether pg_stat_user_indexes.idx_scan on the HNSW index is growing in proportion to traffic. If it isn't, you have a planner flip waiting to happen.