Luddig matchning med pg_trgm-likhet
Skapa stavningstolerant sökning och autoslutförande med trigramindex och likhetströsklar.
Luddig matchning med pg_trgm-likhet är en gratis lektion i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit. Detta är lektion 3 av 4. Du kan läsa vilka 3 lektioner som helst i den här lärvägen kostnadsfritt i sin helhet – därefter låser CoddyKit PRO upp alla lektioner, plus praktisk övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Den ingår i lärvägen för Prestandaoptimering och frågeoptimering i PostgreSQL, och Era framsteg synkroniseras mellan webben och CoddyKit-appen. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.
Varför ungefärlig matchning?
Användare stavar fel. De skriver jonh i stället för john eller postgers i stället för postgres. En vanlig =- eller till och med LIKE-jämförelse returnerar ingenting för dessa stavfel.
Ungefärlig matchning hittar rader som är tillräckligt lika söktermen, inte bara exakta träffar. PostgreSQL tillhandahåller denna funktion i tillägget pg_trgm, som används för:
- Stavningstolerant sökning — matchning trots mindre stavfel
- Autocomplete — förslag medan användaren skriver
- Avduplicering — hitta nästan identiska namn eller adresser
Grundidén är att mäta likhet i stället för likhet med avseende på exakt samma värde.
Vad är en trigram?
En trigram är en grupp av tre intilliggande tecken i en sträng. pg_trgm delar upp varje sträng i mängden av dess trigrammer och fyller ut början och slutet med blanksteg.
För ordet cat skapar PostgreSQL trigrammerna: " c", " ca", "cat", "at ". Du kan själv inspektera detta med show_trgm().
Två strängar betraktas som lika när de delar många trigrammer. Eftersom trigrammer överlappar varandra förstör ett enskilt stavfel bara några få av dem, så liknande ord delar fortfarande de flesta av sina trigrammer.
-- Enable the extension once per database
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- Inspect the trigrams of a word
SELECT show_trgm('cat');
-- {" c"," ca","at ","cat"}Funktionen similarity()
Kärnmåttet är similarity(a, b). Det returnerar ett real-tal mellan 0 (inga gemensamma trigram) och 1 (identiska strängar).
Internt divideras antalet gemensamma trigram med antalet trigram i unionen av de båda trigrammängderna (ett Jaccard-liknande förhållande). Ju mer stavningen liknar varandra, desto högre blir poängen.
Lägg märke till att ett enda stavfel bara sänker poängen lite, medan ett orelaterat ord får en poäng nära noll.
SELECT
similarity('postgres', 'postgres') AS exact, -- 1
similarity('postgres', 'postgers') AS typo, -- ~0.45
similarity('postgres', 'banana') AS unrelated; -- 0Likhetsoperatorn %
Att skriva similarity(a, b) > threshold överallt blir omständligt och kan, ännu viktigare, inte använda ett trigramindex direkt. I stället tillhandahåller pg_trgm operatorn %.
a % b returnerar true när likheten mellan de två strängarna överstiger det aktuella likhetströskelvärdet. Operatorn är indexmedveten, så ett GIN- eller GiST-trigramindex kan snabba upp den.
Standardtröskeln är 0.3. Läs sessionsvärdet med show_limit() (äldre API) eller GUC:en pg_trgm.similarity_threshold.
-- These two rows are 'similar enough' at the default 0.3 threshold
SELECT 'postgres' % 'postgers' AS is_similar; -- t
-- See the current threshold
SHOW pg_trgm.similarity_threshold; -- 0.3Justera likhetströskeln
Tröskeln styr avvägningen mellan recall (att hitta fler träffar) och precision (att undvika irrelevanta träffar).
- Lägre tröskel (t.ex. 0.2) → mer tillåtande, fler resultat, fler falska positiva
- Högre tröskel (t.ex. 0.5) → striktare, färre resultat, risk att missa verkliga stavfel
Ange den per session med SET pg_trgm.similarity_threshold. Operatorn % använder omedelbart det nya värdet, och alla indexskanningar förblir giltiga.
-- Tighten matching for this session
SET pg_trgm.similarity_threshold = 0.45;
SELECT name
FROM products
WHERE name % 'wireles keyboad'
ORDER BY similarity(name, 'wireles keyboad') DESC;Trigramindex: GIN jämfört med GiST
Utan ett index tvingar % fram en sekventiell skanning som beräknar likheten för varje rad — det fungerar bra för hundratals rader, men blir plågsamt för miljontals. pg_trgm stöder två indextyper:
- GIN (
gin_trgm_ops) — snabbare uppslagning och snabbare att bygga för läsintensiv sökning; vanligtvis standardvalet. - GiST (
gist_trgm_ops) — stöder avståndsbaserad sortering för KNN (<->) och kan vara billigare att uppdatera.
För typisk stavningstolerant sökning bör Ni använda GIN. Bygg indexet på kolumnen Ni söker i.
-- GIN index for fast % and LIKE/ILIKE acceleration
CREATE INDEX idx_products_name_trgm
ON products
USING gin (name gin_trgm_ops);Så snabbar indexet upp % och LIKE
Ett trigram-GIN-index gör mer än att bara snabba upp användningen av %. Eftersom PostgreSQL kan extrahera trigram från ett LIKE- eller ILIKE-mönster snabbar samma index också upp jokerteckensökningar som '%board%' – inklusive inledande jokertecken som ett vanligt B-träd inte kan använda.
Kör EXPLAIN ANALYZE och leta efter en Bitmap Index Scan på ditt trigramindex i stället för en Seq Scan. Det bekräftar att frågeplaneraren använder indexet.
EXPLAIN ANALYZE
SELECT name
FROM products
WHERE name ILIKE '%keyboard%';
-- -> Bitmap Index Scan on idx_products_name_trgmRangordna resultat efter likhet
Matchning är bara halva arbetet — användare förväntar sig den bästa träffen först. Filtrera med % (indexvänligt) och sortera sedan med similarity() i ORDER BY.
Behåll predikatet WHERE name % :q så att indexet begränsar kandidaterna, och rangordna sedan de återstående resultaten. Det är billigt att beräkna similarity() enbart för den filtrerade mängden.
SELECT name, similarity(name, 'mechancal keybord') AS score
FROM products
WHERE name % 'mechancal keybord'
ORDER BY score DESC
LIMIT 10;KNN-sortering efter avstånd med <->
För frågor som enbart ska returnera de N närmaste namnen erbjuder pg_trgm avståndsoperatorn <->, som definieras som 1 - similarity(a, b). Ett mindre avstånd innebär större likhet.
När du använder ORDER BY column <-> :q kan ett GiST-trigramindex returnera rader direkt i avståndsordning (en KNN-indexskanning) – utan sorteringssteg och utan att någon uttrycklig tröskel behövs. Det är idealiskt för autokomplettering och sökningar efter närmaste matchning.
-- Requires a GiST trigram index for the KNN scan
CREATE INDEX idx_products_name_gist
ON products USING gist (name gist_trgm_ops);
SELECT name
FROM products
ORDER BY name <-> 'wireles mouse'
LIMIT 5;word_similarity för autoslutförande
Vanlig similarity() straffar längdskillnader: när den korta frågan app matchas mot den långa strängen apple smartphone pro blir poängen låg eftersom de flesta trigrammen tillhör den längre texten.
word_similarity(a, b) löser detta genom att hitta den bäst matchande sammanhängande delen av b. Den har sin egen operator <% och sin egen GUC pg_trgm.word_similarity_threshold (standardvärde 0.6) — perfekt för autoslutförande där frågan är ett prefix eller ett enskilt ord.
SELECT
similarity('app', 'apple smartphone') AS plain, -- low
word_similarity('app', 'apple smartphone') AS word; -- higher
-- Index-friendly autocomplete filter
SELECT name FROM products WHERE 'app' <% name;Praktiska fallgropar
Några saker orsakar ofta problem med pg_trgm:
- Mycket korta frågor (1–2 tecken) har nästan inga trigram, så likheten blir opålitlig — ställ ett minimikrav på längden för autoslutförande.
- Versaler och accenter: trigrammatchning är skiftlägesokänslig för likhetsberäkning, men normalisera accenter (t.ex. via
unaccent) om era data behöver det. - Indexval: välj inte GiST om Ni inte behöver KNN-sortering med
<->; GIN är vanligtvis snabbare för%. - Tröskel per användningsområde: sökning, autoslutförande och deduplicering behöver ofta olika trösklar — ange dem per session, inte globalt.
Snabbtest
Testa Er förståelse av prestanda vid trigram-sökning.
Sammanfattning
Ni kan nu bygga snabb, stavningstolerant sökning i PostgreSQL med pg_trgm:
- Trigram delar upp strängar i delar om tre tecken; gemensamma delar innebär likhet.
similarity()ger en poäng mellan 0 och 1; operatorn % filtrerar enligtpg_trgm.similarity_threshold(standardvärde 0.3) och är indexmedveten.- Bygg ett GIN-index (
gin_trgm_ops) för att snabba upp%,LIKEochILIKE— även med inledande jokertecken. - Filtrera med
%och rangordna sedan medsimilarity()iORDER BY; eller använd avståndet<->med ett GiST-index för KNN-sortering. - Använd
word_similarity/<%för autoslutförande och justera trösklarna efter användningsområde.
Bekräfta alltid med EXPLAIN ANALYZE att Ni får en Bitmap- (eller KNN-) Index Scan i stället för en Seq Scan.
Lär dig SQL med en AI-lärare – gratis
Skriv och kör riktig kod i webbläsaren, få omedelbar hjälp av en AI-lärare dygnet runt och fortsätt där du slutade – på webben eller i appen.
- Kurser
- 22
- Lektioner
- 88
Vanliga frågor
Är lektionen ”Luddig matchning med pg_trgm-likhet” gratis?
Ja – du kan läsa vilka 3 lektioner som helst i lärvägen Prestandaoptimering och frågeoptimering i PostgreSQL, inklusive ”Luddig matchning med pg_trgm-likhet”, kostnadsfritt i sin helhet här på webben. Därefter låser CoddyKit PRO upp alla lektioner, plus interaktiv övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.
Vad lär jag mig i ”Luddig matchning med pg_trgm-likhet”?
Skapa stavningstolerant sökning och autoslutförande med trigramindex och likhetströsklar. Ni övar på Prestandaoptimering och frågeoptimering i PostgreSQL med praktisk kod som körs direkt i webbläsaren, medan en AI-handledare som är tillgänglig dygnet runt svarar på Era frågor under lektionen.
Behöver jag någon erfarenhet för att börja lära mig Prestandaoptimering och frågeoptimering i PostgreSQL?
Du behöver inga förkunskaper. Utbildningen i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit är upplagd för allt från nybörjare till avancerade elever, så att du kan börja här eller från början och gå fram i din egen takt. Detta är lektion 3 av 4.
Hur lång tid tar lektionen ”Luddig matchning med pg_trgm-likhet”?
De flesta CoddyKit-lektioner tar cirka 5–10 minuter. Varje lektion är kort och interaktiv, så att du gör stadiga framsteg och kan fortsätta precis där du slutade – på webben eller i appen.
Kan jag skriva och köra kod i den här Prestandaoptimering och frågeoptimering i PostgreSQL-lektionen?
Ja. Varje Prestandaoptimering och frågeoptimering i PostgreSQL-lektion innehåller en inbyggd kodredigerare, så att du kan skriva och köra riktig kod direkt i webbläsaren och få omedelbar AI-feedback – utan lokal installation.
Alla lektioner i den här kursen
- Utforma tsvector-kolumner och GIN-index
- Rangordning och relevansjustering med ts_rank
- Luddig matchning med pg_trgm-likhet
- Kombinera filter med sökpredikat