Trigram Algorithm: Fuzzy Matching magic pagh

#algorithms#databases#sql#python

trigram three consecutive characters neH. cha' strings trigram sets compare vaj fast typo-tolerant similarity score 0 'ej 1 Dung. PostgreSQL pg_trgm idea ghaH. ~30 lines Python Daq fit. post scratch vo' build, search-as-you-type Dun qatlh, Postgres nuq Qu' map.

core idea

word take, pad, three-character window slide. cat (two spaces front, one back):

sorted(trigrams("cat"))
# ['  c', ' ca', 'at ', 'cat']

padding qatlh. word start 'ej end own features count: c words c start neH; at words at end neH. padding pagh vaj word first last letters middle letters law' trigrams Daq tlhoy'.

cha' strings compare: both trigram sets compute, shared / total ratio. Jaccard index:

similarity(a, b) = |T(a) ∩ T(b)| / |T(a) ∪ T(b)|

identical strings 1.0, no common trigram 0.0, others between.

working implementation

whole thing, pg_trgm style: input lowercase, alphanumeric words split, word Hoch pad, Hoch words trigrams wa' set Daq collect.

import re

def trigrams(text):
    grams = set()
    for word in re.findall(r"[a-z0-9]+", text.lower()):
        padded = f"  {word} "
        grams.update(padded[i:i + 3] for i in range(len(padded) - 2))
    return grams

def similarity(a, b):
    ta, tb = trigrams(a), trigrams(b)
    if not ta or not tb:
        return 0.0
    return len(ta & tb) / len(ta | tb)

version ASCII letters digits neH (short). real implementation umlauts other scripts word characters decide poQ.

results:

similarity("word", "two words")      # 0.3636
similarity("Kartoffel", "Kartofel")  # 0.7273
similarity("postgres", "postgresql") # 0.6667
similarity("postgres", "progress")   # 0.2

first number PostgreSQL documentation example similarity('word', 'two words') rap match - padding rules correct sanity check.

last two lines interesting part. postgres 'ej postgresql related, 0.67. postgres 'ej progress almost same letters 'ach different order, 0.2 neH. plain character-frequency comparison close call. trigrams local order capture - names words vaD useful.

qatlh three?

window length trade-off:

bigrams (two characters): almost every word pair share something - scores noisy, candidate lists long.
trigrams: features specific enough discriminate, single typo few damage.
four+ characters: short words very few features, typo larger share destroy.

single wrong letter middle three trigrams max break (three windows cover position). longer word typo survive. Kartofel vs Kartoffel 0.73 (letter missing 'ach).

trigrams edit-distance algorithm pagh. operations count tell pagh; very short strings worst. strength else: trigram sets index laH.

inverted index fast

query every row compare few hundred entries Dun, millions fall. trick: trigram inverted index key (entries contain point). query entries at least wa' trigram share neH legh:

from collections import defaultdict

class TrigramIndex:
    def __init__(self, items):
        self.items = list(items)
        self.grams = [trigrams(x) for x in self.items]
        self.postings = defaultdict(set)
        for i, g in enumerate(self.grams):
            for t in g:
                self.postings[t].add(i)

    def search(self, query, threshold=0.3):
        q = trigrams(query)
        candidates = set()
        for t in q:
            candidates |= self.postings.get(t, set())
        hits = []
        for i in candidates:
            g = self.grams[i]
            score = len(q & g) / len(q | g)
            if score >= threshold:
                hits.append((score, self.items[i]))
        return sorted(hits, reverse=True)

index field cheaply narrow, exact score candidates neH compute. database names small list try:

idx = TrigramIndex(["PostgreSQL", "MySQL", "MariaDB", "SQLite",
                    "Postgres-XL", "Redis", "MongoDB"])

idx.search("postgre")
# [(0.583, 'PostgreSQL'), (0.538, 'Postgres-XL')]

idx.search("postgress")
# [(0.615, 'PostgreSQL'), (0.571, 'Postgres-XL')]

truncated postgre 'ej misspelled postgress right entries find, MySQL 'ej SQLite out (sql letters PostgreSQL rap share 'ach). threshold 0.3 pg_trgm default same.

PostgreSQL Daq same

database Daq build poQ pagh. pg_trgm extension standard contrib modules rap:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT similarity('Kartoffel', 'Kartofel');
SELECT show_trgm('cat');

show_trgm trigram set return, similarity score - own implementation check laH. % operator true (score threshold law': pg_trgm.similarity_threshold, default 0.3), <-> distance (1 minus similarity):

CREATE TABLE products (id serial PRIMARY KEY, name text NOT NULL);

CREATE INDEX products_name_trgm_idx
  ON products USING gin (name gin_trgm_ops);

SELECT name, similarity(name, 'postgress') AS score
FROM products
WHERE name % 'postgress'
ORDER BY score DESC
LIMIT 10;

GIN index Python example inverted index (database maintain). bonus: same index LIKE '%term%' 'ej ILIKE queries speed up (B-tree leading wildcard help pagh). nearest-neighbor ordering ORDER BY name <-> 'postgress' vaj GiST index gist_trgm_ops Dun.

nuq lo', nuq Qo'

trigram matching Dun:

names, titles, product lists search-as-you-type
typo-tolerant lookups (user input cities list match)
likely duplicates customer pagh company names table Daq find

Dun pagh:

very short strings (wa' cha' characters whole score decide)
semantic search (car 'ej automobile trigram share pagh)
long documents (full-text search stemming ranking Dun)

yaj

trigram algorithm fuzzy string matching set arithmetic choH. words pad, three-character windows cut, sets Jaccard compare. sets inverted index Daq put laH - same idea Python list vo' millions rows table Daq scale. PostgreSQL already vaj pg_trgm enable 'ej GIN index add (separate search service qaSpa').