tsvector-kolommen en GIN-indexen ontwerpen

Het duidelijkste moderne ontwerp (PostgreSQL 12+) is een opgeslagen gegenereerde kolom. PostgreSQL berekent de tsvector automatisch wanneer de rij verandert, zodat het document altijd overeenkomt met de brontekst.

Houd rekening met twee regels:

• De generatie-expressie moet IMMUTABLE zijn. Daarom geef je de regconfig als letterlijke waarde door ('english') in plaats van te vertrouwen op een sessie-instelling.
• Gebruik coalesce(), zodat een NULL-veld niet het hele document NULL maakt.

Niet elk veld is even belangrijk. Een overeenkomst in de titel is meestal belangrijker dan een overeenkomst diep in de hoofdtekst. setweight() voorziet lexemen van het label A, B, C of D (A is het hoogste).

Met deze labels kan ts_rank later overeenkomsten in de titel hoger waarderen dan overeenkomsten in de hoofdtekst. Verwerk de weging in de gegenereerde kolom, zodat die één keer en niet tijdens elke query wordt berekend.

Een opgeslagen tsvector is zonder index nog steeds nutteloos. Het juiste indextype voor zoeken in volledige tekst is GIN (Generalized Inverted Index). GIN slaat één vermelding per verschillend lexeem op, met verwijzingen naar de rijen waarin het voorkomt. Dat is precies wat overeenkomsten met @@ nodig hebben.

Omdat de kolom al een tsvector bevat, is de index een gewone kolomindex en is er geen expressie nodig:

De query verwijst nu rechtstreeks naar de opgeslagen kolom. De planner kan de GIN-index gebruiken omdat de expressie in de WHERE-clausule (search_doc) exact overeenkomt met de geïndexeerde expressie.

Geen to_tsvector() per rij en geen sequentiële scan. Voer EXPLAIN ANALYZE uit; je zou een Bitmap Index Scan op idx_articles_search_doc moeten zien.

PostgreSQL ondersteunt twee indextypen voor tsvector. Kies bewust:

• GIN: sneller zoeken, de standaardkeuze voor zoekopdrachten. Iets groter en trager op te bouwen en bij te werken. Het beste wanneer lezen overheerst.
• GiST: kleiner en goedkoper bijwerken, maar verliesgevend, waardoor kandidaatrijen opnieuw worden gecontroleerd en zoekopdrachten trager zijn. Handig voor gegevens met heel veel schrijfbewerkingen of voortdurende wijzigingen.

Voor de meeste zoekbelastingen, waarbij je veel vaker queryt dan schrijft, wint GIN. Kies GiST alleen wanneer de kosten voor het bijwerken van de index je knelpunt zijn.

GIN-indexen gebruiken een wachtlijst om invoegingen te bundelen (fastupdate = on staat standaard aan). Dit versnelt schrijfbewerkingen, maar een grote wachtlijst vertraagt leesbewerkingen omdat queries deze naast de hoofdindex moeten scannen.

Voor zoektabellen met veel leesbewerkingen kun je dit gedrag afstemmen of uitschakelen. Als je fastupdate uitschakelt, kost elke invoeging meer werk, maar blijven queries consequent snel.

Gegenereerde kolommen zijn in PostgreSQL 12 geïntroduceerd. In oudere versies, of wanneer je logica nodig hebt die niet IMMUTABLE is, onderhoud je de tsvector met een trigger.

De klassieke hulpfunctie is tsvector_update_trigger, die een doelkolom vult vanuit benoemde bronkolommen. Let op de beperking: de functie gebruikt één vaste weging en één vaste configuratie. Voor weging per veld schrijf je daarom zelf een aangepaste BEFORE-triggerfunctie.

Een trigger wordt alleen uitgevoerd bij toekomstige invoegingen en updates. Bestaande rijen houden een NULL-waarde in search_doc totdat je ze aanvult.

Bij een opgeslagen gegenereerde kolom vult PostgreSQL de waarden automatisch aan wanneer je de kolom toevoegt. Bij het triggerpatroon voer je eenmalig een UPDATE uit. Doe dit bij zeer grote tabellen in batches op basis van bereiken van de primaire sleutel, zodat je niet de hele tabel vergrendelt of één enorme transactie opblaast.

Controleer altijd of de planner je GIN-index gebruikt in plaats van terug te vallen op een sequentiële scan. Veelvoorkomende redenen waarom dat niet gebeurt: de query-expressie komt niet overeen met de geïndexeerde expressie, de tabel is klein of de statistieken zijn verouderd.

Voer EXPLAIN (ANALYZE, BUFFERS) uit en zoek naar een Bitmap Index Scan op de naam van je index. Zie je een Seq Scan met een Filter, dan wordt de index niet gebruikt. Zorg dan dat de expressies overeenkomen of voer ANALYZE uit.

Je hebt van begin tot eind een zeer snelle kolom voor full-textzoeken ontworpen:

• Vooraf berekenen van het document in een STORED gegenereerde tsvector-kolom, zodat tekst één keer wordt geparseerd en niet per query.
• Wegen van velden met setweight() (A voor titel, B voor hoofdtekst), zodat ts_rank overeenkomsten betekenisvol kan scoren.
• Indexeren van de kolom met GIN, de voor lezen geoptimaliseerde omgekeerde index voor overeenkomsten met @@; gebruik GiST alleen bij zeer veel schrijfbewerkingen en voortdurende wijzigingen.
• Afstemmen van schrijfbewerkingen via fastupdate en gin_pending_list_limit wanneer de wachtlijst het lezen vertraagt.
• Onderhouden van tabellen van vóór versie 12 met een trigger en aanvullen van bestaande rijen in batches.
• Controleren met EXPLAIN (ANALYZE, BUFFERS) of je een Bitmap Index Scan krijgt en geen Seq Scan.