PostgreSQL: LIKE '%text%' ist langsam. Kann pg_trgm dafür sorgen, dass ein Index greift?

In der Welt der Datenbanken sticht PostgreSQL durch seine Anpassungsfähigkeit und die Fülle an Features hervor, besonders beim Umgang mit Textdaten. Ein oft unterschätztes Juwel ist in diesem Zusammenhang die Erweiterung pg_trgm.

Was ist pg_trgm?

pg_trgm ist eine PostgreSQL-Erweiterung, mit der sich die Ähnlichkeit zwischen Zeichenketten bestimmen und Textsuchen verbessern lassen. Sie zerlegt Zeichenketten in Trigramme, also Gruppen von drei aufeinanderfolgenden Zeichen aus dem Text. Das Wort “Daniele” würde zum Beispiel in diese Trigramme zerlegt: d, da,dan,ani,nie,iel,ele,le .

Dieser Ansatz ermöglicht unscharfe Suchen (Fuzzy Search), die Ergebnisse finden, die dem Suchbegriff “ähneln”, selbst bei Tippfehlern oder kleinen Abweichungen.

Wie verwendet man sie?

Zuerst muss die Erweiterung in deiner Datenbank mit diesem Befehl aktiviert werden:

CREATE EXTENSION pg_trgm;

Danach kannst du die Funktionen und Operatoren von pg_trgm nutzen, um deine Suchen zu verbessern. Um etwa Datensätze zu finden, die einem Suchbegriff ähneln, verwendest du den Operator %:

SELECT * FROM your_table WHERE your_text_column % 'word';

Das liefert alle Datensätze, bei denen your_text_column ‘word’ ähnelt.

Wenn du neugierig bist, wie Text zerlegt wird, verwende show_trgm wie unten gezeigt.

SELECT show_trgm('Daniele');


--- AUSGABE 
--- {"  d"," da","ani","dan","ele","iel","le ","nie"}

Praktische Beispiele

Angenommen, wir haben eine Tabelle songs mit einer Spalte title. Um Titel zu finden, die “Sultans of swing” ähneln, könnte man schreiben:

SELECT title FROM songs WHERE title % 'Sultens of swing';

Dieser Befehl kann trotz der Tippfehler das richtige “Sultans of swing” finden.

Um zu verstehen, wie das funktioniert, sehen wir uns den Operator % an.

Der Operator % wird auf zwei text-Werte angewendet und liefert einen boolean. Aus der PG-Dokumentation: “Returns true if its arguments have a similarity that is greater than the current similarity threshold set by pg_trgm.similarity_threshold.”

Der Parameter similarity_threshold steht standardmäßig auf 0.3. Zwei Wörter gelten also als “ähnlich”, wenn ihre Ähnlichkeit bei 30 % oder mehr liegt. Das kann für dein Szenario passen oder auch nicht. Falls nötig, lässt sich der Parameter ganz einfach ändern.

Den aktuellen Wert von pg_trgm.similarity_threshold liest du mit folgender Anweisung:

show pg_trgm.similarity_threshold;

Sagen wir, wir wollen pg_trgm.similarity_threshold auf 0.5 setzen.

Um die Performance bei großen Tabellen zu optimieren, empfiehlt sich ein GIST- oder GIN-Index auf den Trigrammen:

CREATE INDEX idx_title_trgm ON songs USING gist (title gist_trgm_ops);

Nach dieser kleinen Einführung in pg_trgm und den Operator % kommen wir zum eigentlichen Thema dieses Artikels.

Weitere Informationen zu den verfügbaren pg_trgm-Operatoren gibt es hier.

LIKE-Abfragen mit pg_trgm beschleunigen

Über unscharfe Suchen hinaus hat die Erweiterung pg_trgm noch einen Trick im Ärmel: Sie kann Suchen mit den Operatoren LIKE und ILIKE in PostgreSQL deutlich beschleunigen, vor allem solche mit Platzhaltern % an beiden Enden des Suchbegriffs. Das ist besonders nützlich, weil solche Suchen ohne pg_trgm bei großen Datenmengen sehr langsam sein können, da sie einen sequenziellen Scan der ganzen Tabelle erfordern.

LIKE/ILIKE-Abfragen optimieren

Ist pg_trgm installiert und richtig konfiguriert, kann PostgreSQL trigrammbasierte GIN- und GIST-Indizes nutzen, um LIKE- und ILIKE-Abfragen zu beschleunigen. Diese Methode nutzt die Trigramm-Darstellung der Daten, um die Datenmenge, die bei der Suche untersucht werden muss, drastisch zu verkleinern.

Nehmen wir wieder die Tabelle songs von vorhin: Wollen wir alle Titel finden, die die Zeichenkette “love” enthalten, sähe eine typische Abfrage so aus:

SELECT title FROM songs WHERE title LIKE '%love%';

Ohne pg_trgm kann diese Abfrage bei großen Datenmengen langsam sein. Mit einem GIN- oder GIST-Trigramm-Index auf der Spalte title kann PostgreSQL die Suche dagegen schnell auf die Datensätze eingrenzen, die mit höherer Wahrscheinlichkeit zum Muster passen, und die Ausführungszeit der Abfrage deutlich senken.

Einen Index zur Optimierung von LIKE/ILIKE anlegen

Um diese Optimierung zu nutzen, legen wir einen GIN-Index auf der betreffenden Spalte an:

CREATE INDEX idx_title_like ON songs USING gin (title gin_trgm_ops);

Mit diesem Index lassen sich LIKE- und ILIKE-Abfragen mit Platzhaltern an beiden Enden des Musters viel schneller ausführen, dank der trigrammbasierten Vorauswahl der Kandidaten.

Ein Test unter realen Bedingungen

Für einen Test unter realen Bedingungen legst du mit folgendem Skript eine Tabelle mit einer halben Million Datensätzen an.

drop table if exists random_people;
create table random_people as
  with rnd_people as (
    select distinct  
	    ('[0:13]={"Daniele","Debora","Mattia","Jake","Amy","Samuel","Jacopo","Martina","Sofia","Henry","Neil","Tim","John","George"}'::text[])
		    [floor(random()*14)] || ' ' || floor(random()*100000+1)::text || '°'  first_name,
	    ('[0:13]={"Bianchi","Lamborghini","Ferrari","De Tommaso","Rossi","Verdi","Gialli","Caponi","Gallini","Gatti","Ford","Daniel","Harrison","Macdonald"}'::text[])
		    [floor(random()*14)] || ' ' || floor(random()*100000+1)::text || '°' last_name
    from 
      generate_series(1, 10 * 1000 * 50)
  )
  select 
    row_number() over() id, first_name, last_name 
  from 
    rnd_people;

select count(*) from random_people; -- 500.000 Datensätze   

Laut EXPLAIN ANALYZE braucht die Abfrage ca. 160 ms.

explain analyze select * from random_people where last_name ilike '%tom%';  -- ca. 160 ms auf meinem Rechner 

Jetzt legst du einen GIN-Index auf dem Feld last_name an und verwendest dabei die Operator-Klasse gin_trgm_ops.

CREATE EXTENSION if not exists pg_trgm;
drop index if exists index_last_name_on_random_people_trigram;
CREATE INDEX CONCURRENTLY index_last_name_on_random_people_trigram
ON random_people
USING gin (last_name gin_trgm_ops);

Und nun der entscheidende Test. Führe die vorige Abfrage erneut aus.

explain analyze select * from random_people where last_name ilike '%tom%'; -- ca. 0,025 ms auf meinem Rechner!

Großartig! Von 160 ms auf 0,025 ms (auf meinem Rechner).

Abschließende Gedanken

pg_trgm zur Optimierung von LIKE- und ILIKE-Abfragen einzusetzen ist ein Paradebeispiel dafür, wie PostgreSQL-Erweiterungen die Performance und Flexibilität von Anwendungen verbessern können. Man sollte aber nicht vergessen: Trigrammbasierte Indizes beschleunigen diese Suchen zwar deutlich, brauchen aber zusätzlichen Platz und können das Einfügen von Daten verlangsamen. Wie immer in der Technik sollte man ihren Einsatz also sorgfältig anhand der konkreten Anforderungen der Anwendung abwägen.

Kurz gesagt: Wer pg_trgm in seine Index- und Suchstrategien einbaut, kann die Performance von Textabfragen grundlegend verändern. “Sapere aude” (Wage zu wissen): Diese Erweiterung lädt dich ein, die fortgeschrittenen Fähigkeiten von PostgreSQL bei der Verwaltung von Textdaten zu erkunden und voll auszuschöpfen.

PostgreSQL-Schulungen

Wenn du PostgreSQL wirklich beherrschen willst, bietet meine Firma einige sehr gefragte Schulungen an:

• PostgreSQL for Developers, Englisch
• PostgreSQL per Sviluppatori, Italienisch
• PostgreSQL per Amministratori, Italienisch

Wenn deine Firma eine maßgeschneiderte Schulung braucht, sag uns Bescheid.

Die Schulungen gibt es remote und vor Ort.

Links

• Offizielle Dokumentation von pg_trgm
• Alle Funktionen der Erweiterung pg_trgm