PostgreSQL : que valent les nouvelles extensions de regex pg_tre et pg_re2
Les requêtes basées sur des expressions régulières peuvent rapidement paralyser PostgreSQL sur de gros volumes de données. Le test de deux extensions récentes, pg_tre et pg_re2, met en lumière des stratégies distinctes pour accélérer la recherche textuelle, entre tolérance aux fautes d'orthographe et gains de performance bruts.
Fil « Nouvelles extensions pg_tre et pg_re2 pour les expressions régulières dans PostgreSQL »
Le banc d'essai : 33 Go de données réelles
L'évaluation a été réalisée sur une table de 1,6 million de lignes contenant des plans d'exécution PostgreSQL pour un poids total de 33 Go (longueur moyenne de 22 ko par document). Sur une requête par expression régulière classique à l'aide de l'opérateur natif ~, PostgreSQL effectue un parallel sequential scan (un parcours séquentiel complet de la table réparti sur plusieurs processeurs) en 41,4 secondes.
L'utilisation classique de l'extension officielle pg_trgm (qui découpe le texte en trigrammes, des séquences de trois caractères consécutifs) permet de créer un index GIN (Generalized Inverted Index, un index inverse adapté aux éléments composites) de 1,6 Go en 16 minutes et 47 secondes. La même requête descend alors à 1,6 seconde grâce à un bitmap index scan (une lecture optimisée croisant l'index et les blocs mémoire).
pg_tre : la recherche floue au prix fort
La première extension testée, pg_tre, promet une gestion avancée des motifs textuels. Son installation nécessite une compilation depuis les sources, suivie d'une déclaration standard dans la base de données :
CREATE EXTENSION pg_tre;
CREATE INDEX plan_tre ON all_plans_tre USING tre (plan);
Le constat sur ce jeu de données est sans appel concernant la construction : la création de l'index a nécessité plus de 7 heures (07:15:27) et l'index généré occupe 21 Go, soit plus de 12 fois la taille d'un index trigramme classique. De plus, les requêtes exactes y sont plus lentes (2,29 secondes). pg_tre se distingue néanmoins par son support natif de la recherche floue (fuzzy matching) basée sur la distance de Levenshtein (un algorithme mesurant le nombre minimal d'insertions, suppressions ou substitutions pour passer d'une chaîne à une autre) :
SELECT word FROM word_stats WHERE word %~~ tre_pattern('postgresql', 1);
pg_re2 : la vitesse brute du moteur de Google
L'extension pg_re2 s'appuie quant à elle sur la bibliothèque C++ RE2 de Google, conçue pour garantir des temps d'exécution linéaires sans retour en arrière (backtracking). Son installation s'effectue simplement via le gestionnaire de paquets de PostgreSQL :
sudo apt-get install libre2-dev
sudo pgxnclient install re2
Sans aucun index, l'opérateur de recherche @~ fourni par l'extension ramène le temps de parcours séquentiel de 41,4 secondes à 23,1 secondes. Lorsqu'on lui associe un index GIN dédié via l'opérateur gin_re2_ops (index généré en 14 minutes et 58 secondes pour une taille de 2,9 Go), la requête s'exécute en seulement 0,95 seconde (958 ms), surpassant nettement pg_trgm.
Sur des expressions régulières plus complexes, l'écart se creuse de manière spectaculaire : une recherche demandant 39,3 secondes avec pg_trgm ne prend que 2,26 secondes avec pg_re2. Il faut cependant garder en tête une contrainte technique majeure de la bibliothèque RE2 : elle ne supporte pas certaines fonctionnalités avancées comme les lookbehinds ((?<=...), des assertions permettant de vérifier ce qui précède un motif sans l'inclure dans le résultat), imposant de réécrire certaines expressions.