Postgres 19 query hints: 19 patronen die we voor RAG gebruiken

Zondagochtend, 03:48 Amsterdam. Een schade-agent voor een Nederlandse opstalverzekeraar vraagt de retrieval-laag om de drie meest relevante clausules in een brandschadepolis uit 2014. De vector-tabel bevat negen miljoen embedded paragrafen, verspreid over veertien jaar aan contracten, addenda en inspectierapporten. De Postgres planner kantelt het plan. De p99 springt van 180 ms naar 4,2 seconden. De klant heeft al een tweede support-tab geopend tegen de tijd dat het eerste antwoord van de agent binnenkomt.

We zijn die tabel acht maanden lang aan het stabiliseren. Wat we, soms met pijn en moeite, hebben geleerd: de Postgres planner heeft een eigen mening over HNSW recall versus sequential scans. Die mening komt niet altijd overeen met die van ons, en hij verschuift tussen minor versions. Het spiekbriefje hieronder is wat we nu standaard meebakken in elke retrieval-module. Negentien patronen, gegroepeerd naar hoeveel weerstand de planner er vroeger tegen bood.

De pgsql-hackers thread rond Postgres 19 query hints (deze week op de voorpagina van Hacker News) bevestigt waar we al voor bouwden: de upstream community trekt nu eindelijk naar core wat de pg_hint_plan extensie al vijftien jaar doet. Goed nieuws. Het is ook de reden om op te schrijven wat we hebben geleerd, vóór de syntax versplintert.

De cijfers hieronder komen uit het productiesysteem: pgvector 0.8, Postgres 17.4 op het live cluster, een Postgres 19 beta op staging, een HNSW index op een embedding-kolom van 1536 dimensies, vier read replica's, een pool gedimensioneerd op negentig gelijktijdige retrieval-calls per seconde.

Zes hints die de planner vroeger liet vallen

Deze zes zijn comment hints in pg_hint_plan stijl. In stock Postgres slaat de planner ze over. Wij laden pg_hint_plan als shared library, zetten pg_hint_plan.enable_hint = on, en dan luistert de planner eindelijk. In Postgres 19 verhuist de equivalente syntax naar core, dus de extensie gaat van onze replica-deploy checklist af.

1. IndexScan: dwing de HNSW index af

De hint met de hoogste impact. De planner besluit soms dat een parallelle sequential scan over negen miljoen rijen goedkoper is dan een HNSW probe, zeker nadat VACUUM ANALYZE de row estimate herschrijft. Hij zit er ongeveer 4% van de tijd naast, maar 4% van negentig calls per seconde is genoeg om de tail om zeep te helpen.

/*+ IndexScan(c claims_embedding_hnsw) */
SELECT c.id, c.body, c.embedding <=> $1 AS distance
FROM claims c
WHERE c.policy_id = ANY($2)
ORDER BY c.embedding <=> $1
LIMIT 3;

2. NoSeqScan: blokkeer de fallback

Voor alle zekerheid, gecombineerd met de IndexScan hint. Als de index even invalid is tijdens een REINDEX CONCURRENTLY, zorgt NoSeqScan ervoor dat de query luid faalt in plaats van stilletjes terugzakt naar een full table scan die in twaalf seconden klaar is en het response-budget van de agent opblaast.

/*+ IndexScan(c claims_embedding_hnsw) NoSeqScan(c) */
SELECT ...

3. HashJoin: schiet de nested loop af bij policy-lookups

Wanneer de agent clausules ophaalt voor een klant met honderden actieve polissen, kiest de planner een nested loop met een memoize-node. Memoize is briljant zolang de inner side in work_mem past. Bij onze cardinaliteit spilt het, en een spillende memoize is slechter dan de hash die hij verving.

/*+ HashJoin(c p) Leading((p c)) */
SELECT c.id, c.body, c.embedding <=> $2 AS distance
FROM policies p
JOIN claims c ON c.policy_id = p.id
WHERE p.customer_id = $1
ORDER BY c.embedding <=> $2
LIMIT 3;

4. Leading: leg de driving table vast

Begin altijd met de kleinere relatie. De planner snapt dat meestal vanzelf, maar na een partition swap op de policies tabel loopt de statistiek uren achter en gaat de join order achterstevoren.

5. Memoize: wanneer je hem juist wél wilt

Het spiegelbeeld van patroon 3. Bij low-cardinality fan-outs (één klant, een dozijn polissen, drie clausules per stuk) houdt een gepinde memoize de p50 onder de 40 ms.

/*+ Memoize(c) NestLoop(p c) */
SELECT ...

6. Parallel(hard): zet het aantal workers vast

Zonder hard pin onderhandelt de planner op plan-tijd met max_parallel_workers_per_gather. Op een drukke replica kiest hij nul workers en serialiseert hij. Met Parallel(c 4 hard) reserveert hij er vier. De replica trekt het prima; we hebben de pool daarop afgestemd.

Zeven session GUC's die we per transactie vastzetten

Deze hebben het altijd gedaan. De planner leest ze elke query, geen extensie nodig. We zetten ze met SET LOCAL binnen de retrieval-transactie, zodat ze bij COMMIT terugklappen en nooit lekken in een pooled connection. De officiële referentie staat in de PostgreSQL runtime-configuration docs.

7. enable_seqscan = off

Het botte zwaard. Wij hebben liever de gerichte NoSeqScan hint, maar bij long-tail queries op gepartitioneerde tabellen is de sessieschakelaar simpeler te overzien en makkelijker terug te draaien.

8. max_parallel_workers_per_gather

Op 4 voor retrieval queries, op 0 voor het embedding-ingest pad. Parallel workers op een INSERT ... SELECT richting een pgvector-tabel vechten om dezelfde WAL writer en verdubbelen onze write-latency.

9. work_mem

64MB per retrieval-transactie. De default van 4MB dwingt de HNSW result heap naar disk. Let op: work_mem geldt per sort- of hash-node, niet per query. Een query met drie hash-nodes gebruikt 192MB.

BEGIN;
SET LOCAL work_mem = '64MB';
SET LOCAL max_parallel_workers_per_gather = 4;
SET LOCAL jit = off;
-- retrieval query hier
COMMIT;

10. jit = off

JIT-compilatie kost 30 tot 80 ms per query op onze workload. De retrieval queries zelf draaien in 12 ms. Reken zelf maar uit.

11. plan_cache_mode = force_custom_plan

Prepared statements vallen na vijf uitvoeringen terug op een generiek plan. Onze retrieval queries hebben extreem verschillende parameter-selectiviteit (één polis tegenover driehonderd), dus dat generieke plan klopt ongeveer een derde van de tijd niet. Het forceren van een custom plan kost 0,4 ms aan planning-overhead en haalt de ergste tail eraf.

12. cursor_tuple_fraction = 1.0

Als je resultaten streamt met een cursor (wij doen dat, voor re-ranking) zegt de default van 0,1 tegen de planner dat hij moet optimaliseren voor de eerste 10% van de rijen. Wij willen de complete set geranked hebben. Op 1.0 zetten geeft ons het all-rows plan.

13. random_page_cost = 1.1

De default is 4.0, gebaseerd op draaiende schijven. Op NVMe ligt de kostenverhouding tussen random en sequential reads dichter bij 1,05. Op 1.1 zetten zorgt dat de planner de HNSW index vertrouwt zonder dat wij hem bij elke call hoeven te hinten.

Zes pgvector- en vangnet-knoppen

14. hnsw.ef_search

De recall-knop. Default staat op 40. Wij hebben hem op 80 gezet voor de schade-agent van de verzekeraar; dat tilde de recall van 91% naar 97,5% en kostte 11 ms extra latency. Het waard, in een claimsflow waar de verkeerde clausule duurder is dan een tragere clausule.

SET LOCAL hnsw.ef_search = 80;

15. ivfflat.probes

Naast HNSW houden we een IVFFlat index aan voor de long-tail queries waar HNSW recall instort op out-of-distribution embeddings. probes=10 zit op het juiste punt op de recall/latency curve voor onze tabel.

16. effective_io_concurrency

Op 200 op NVMe replica's. De default van 1 vertelt de planner dat de opslag een enkele spindel is. Met 200 prefetchen bitmap heap scans correct en zakt de bitmap recheck op de embedding-kolom van 80 ms naar 18 ms.

17. statement_timeout

750 ms. Alles wat langzamer is, is geen retrieval meer maar een ontspoord plan. We vangen de timeout op in de applicatielaag en vallen terug op een grovere keyword-search. De klant ziet dan een iets minder precies antwoord in plaats van helemaal niets.

18. lock_timeout

50 ms. Retrieval hoort nooit op een lock te wachten. Als een onderhoudsjob de index aan het herschrijven is, falen we snel en proberen we een replica.

19. idle_in_transaction_session_timeout

2 seconden. De retrieval-transactie omvat één SELECT plus een handvol SET LOCAL calls. Als hij stilstaat, is er iets in de applicatielogica misgegaan, en willen we de connectie terug in de pool hebben voordat de pool uitdroogt.

BEGIN;
SET LOCAL idle_in_transaction_session_timeout = '2s';
SET LOCAL statement_timeout = '750ms';
SET LOCAL lock_timeout = '50ms';
SET LOCAL hnsw.ef_search = 80;
-- retrieval query
COMMIT;

Postgres 19 dicht het gat

Het query-hints voorstel dat in 19 landt, dekt de eerste zes patronen hierboven (IndexScan, NoSeqScan, HashJoin, Leading, Memoize, Parallel) als native syntax. De committers waren duidelijk: dit is een afgebakende subset, geen volledige port van de pg_hint_plan grammatica. Prima. Die zes waren de patronen waar wij om gaven. De session GUC's in patronen 7 tot en met 13 werken vandaag al op Postgres 12 en hoger. De pgvector-knoppen in 14 en 15 zitten aan de extensiekant en worden niet geraakt door de core-wijziging. Postgres 19 haalt dus, voor onze workload, één shared-library dependency van elke replica af en laat ons de pg_hint_plan deploy-stap schrappen.

Het verandert de discipline niet. Hints zijn nog steeds een contract met de planner. Schrijf ze op, versioneer ze met de code, review ze als het plan verandert.

De vijf-minuten audit

Toen we de retrieval-laag bouwden voor de schade-agent van deze verzekeraar, liepen we ertegenaan dat het ergste gedrag van de planner onzichtbaar bleef onder de p50. Het hele probleem zat in de long tail en kwam pas onder gelijktijdige belasting boven water. Uiteindelijk hebben we een EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) sampler aan de praat gezet die op 1% van de retrieval-calls afgaat en het plan naar een columnstore stuurt. Plan-flips zien we nu binnen een uur, in plaats van wanneer de supportwachtrij volloopt. Draai je vandaag AI-agents tegen een Postgres-kennisbank, dan is het kleinste nuttige dat je vanmiddag kunt doen: die sampler optuigen. De eerste plan-flip die je vangt, verdient hem terug.