Recherche multilingue en texte intégral avec PostgreSQL (partie 2)

2025-01-16

1870 mots, 9 minutes de lecture

Hervé Lefebvre

Utilisation en base de données

Tests en base de données

Création d’une table simple contenant des documents en plusieurs langues

Pour commencer, nous allons créer une table qui va contenir des documents collectés sur le net :

CREATE TABLE pages (id BIGSERIAL PRIMARY KEY, url TEXT UNIQUE, lang CHAR(2) NOT NULL, title TEXT, content TEXT);

La table contiendra simplement un indentifiant, l’url source du document, la langue du document, son titre et son contenu.

Programme python d’alimentation

Pour nous constituer un jeu d’essai, nous allons simplement “scrapper” des pages du site Wikipedia en anglais et en français. Ce script va chercher dans notre table une URL pour lequel le document est absent, va lire l’URL en question, ajouter dans la table les autres URLs présentes dans le document, puis enregister le contenu du document sous forme de texte brut.

Pour scrapper les sites respectivement en anglais et en français, le script se lance simplement :

~/loxodata_text$ python3 get_links.py en
~/loxodata_text$ python3 get_links.py fr

Le script :

from bs4 import BeautifulSoup
import requests
import psycopg2
import sys

def get_links(url,conn,cursor,lang):
    response = requests.get(url)
    data = response.text
    soup = BeautifulSoup(data, 'lxml')
    page1_html = BeautifulSoup(data, 'html.parser')
    page1_txt = page1_html.get_text()
    mots = page1_txt.split() # Supprimer espaces multiples
    for w in mots:
        if len(w) > 1024:
            mots.remove(w) # supprimer les chaines trop longues
    else:
        page1_txt = " ".join(mots)

    titre = soup.title.get_text()
    query = "UPDATE pages SET content=%s,title=%s  WHERE url=%s;"
    cursor.execute(query,(page1_txt,titre,url))

    for link in soup.find_all('a'):
        link_url = link.get('href')
        if link_url is not None and \
                ('/Talk:' in link_url or \
                '/wiki/Discussion' in link_url or \
                '/wiki/CSS' in link_url or \
                'wiki/File:' in link_url or \
                'wiki/Fichier:' in link_url or \
                '/User:' in link_url or \
                '/User_talk:' in link_url): # éliminer les discussions/users, fichiers, ne garder que les articles
            link_url = None
        if link_url is not None and link_url.startswith('/wiki'): # traitement des URL relatives
            link_url='https://'+lang+'.wikipedia.org'+link_url
        if link_url is not None and link_url.startswith('https://'+lang+'.wikipedia.org/wiki'):
            query = "INSERT INTO pages (url,lang) VALUES (%s,%s) ON CONFLICT DO NOTHING;"
            data = (link_url)
            cursor.execute(query,(data,lang.upper()))
            conn.commit()

    return

if __name__ == "__main__":
    if len(sys.argv) < 2:
        lang='en'
    else:
        lang=sys.argv[1]
lang=lang.lower()
r = 'https://'+lang+'.wikipedia.org/wiki/Main_Page'
conn = psycopg2.connect(database='loxodata_text', host='localhost', user='aegir',  port=5432)
cursor = conn.cursor()

# Ajouter l'URL de départ
query = "INSERT INTO  pages(url,lang) VALUES(%s,%s) ON CONFLICT DO NOTHING;"
cursor.execute(query,(r,lang.upper()))
conn.commit()
stop = False
documents=0
max_doc=10000
while not stop:
    query = "SELECT url FROM pages WHERE lang=%s AND content IS NULL LIMIT 1;"
    cursor.execute(query,(lang.upper(),))
    r = cursor.fetchone()
    if r is None:
        stop = True
    else:
        documents=documents+1
        print(documents,r[0]);
        get_links(r[0],conn,cursor,lang)
    if documents > max_doc:
        stop = True

Après avoir touné “un certain temps”, nous avons un jeu d’essai correct pour nos tests :

loxodata_text=# select count(1) as urls, count(1) filter (where content is not null) as total, count(1) filter (where content is not null and lang='FR') as fr, count(1) filter (where content is not null and lang='EN') as en  from pages;
  urls   | total |  fr  |  en
---------+-------+------+------
 1299347 | 14543 | 8393 | 6150
(1 row)

Plus de 14.000 articles ont été chargés, dont 8.000 en français et 6.000 en anglais.

Première recherche simple

Dans cet exemple, je vais effectuer une recherche simple “thé & japonais”. Bien évidemment les résultats dépendent des pages qui auront été “scrappées” sur le wikipédia. La recherche s’effectue sur le contenu de la page. On affiche son titre et son URL. Mais surtout on utilise la fonction ts_rank() qui permet d’obtenir un score de correspondance. Zéro indiquant que le document ne correspond pas du tout, tandis que 1 indique que le document répond à 100%. Ceci permet de limiter la requête aux 10 documents les plus pertinents.

loxodata_text=# select * from (select id,ts_rank(to_tsvector('french_custom',content),to_tsquery('french_custom','thé & japonais') ) , title,url from pages where lang='FR' and  to_tsquery('french_custom','thé & japonais') @@ to_tsvector('french',content) order by 2 desc) foo limit 10 ;
  id   |  ts_rank   |                 title                  |                                   url
-------+------------+----------------------------------------+-------------------------------------------------------------------------
  1716 | 0.99486476 | Thé au Japon — Wikipédia               | https://fr.wikipedia.org/wiki/Th%C3%A9_au_Japon
 13527 |  0.9795128 | Thé vert — Wikipédia                   | https://fr.wikipedia.org/wiki/Th%C3%A9_vert
 13056 |  0.8625401 | Cérémonie du thé japonaise — Wikipédia | https://fr.wikipedia.org/wiki/C%C3%A9r%C3%A9monie_du_th%C3%A9_japonaise
 25315 |  0.8625401 | Cérémonie du thé japonaise — Wikipédia | https://fr.wikipedia.org/wiki/Chad%C3%B4
 22413 |  0.8625401 | Cérémonie du thé japonaise — Wikipédia | https://fr.wikipedia.org/wiki/Chanoyu
 13023 | 0.81228346 | Thé — Wikipédia                        | https://fr.wikipedia.org/wiki/Th%C3%A9
 13979 |  0.7667292 | Catégorie:Thé au Japon — Wikipédia     | https://fr.wikipedia.org/wiki/Cat%C3%A9gorie:Th%C3%A9_au_Japon
  1745 | 0.73748296 | Bancha (thé) — Wikipédia               | https://fr.wikipedia.org/wiki/Bancha_(th%C3%A9)
  1730 |  0.7290929 | Cérémonie du thé — Wikipédia           | https://fr.wikipedia.org/wiki/C%C3%A9r%C3%A9monie_du_th%C3%A9
  1736 | 0.71149355 | Sencha — Wikipédia                     | https://fr.wikipedia.org/wiki/Sencha
(10 rows)

Notre recherche ne s’effectuant que sur le contenu de la page, il pourrait être pertinent d’ajouter le titre de la page au texte dans lequel on fait une recherche, au moins pour calculer le score :

loxodata_text=# select * from (select id,ts_rank(to_tsvector('french_custom',title || ' ' || content),to_tsquery('french_custom','thé & japonais') ) , title,url from pages where lang='FR' and  to_tsquery('french_custom','thé & japonais') @@ to_tsvector('french',content) order by 2 desc) foo limit 10 ;
  id   |  ts_rank   |                 title                  |                                   url
-------+------------+----------------------------------------+-------------------------------------------------------------------------
  1716 |  0.9961827 | Thé au Japon — Wikipédia               | https://fr.wikipedia.org/wiki/Th%C3%A9_au_Japon
 13527 |  0.9795128 | Thé vert — Wikipédia                   | https://fr.wikipedia.org/wiki/Th%C3%A9_vert
 13056 |  0.8625401 | Cérémonie du thé japonaise — Wikipédia | https://fr.wikipedia.org/wiki/C%C3%A9r%C3%A9monie_du_th%C3%A9_japonaise
 25315 |  0.8625401 | Cérémonie du thé japonaise — Wikipédia | https://fr.wikipedia.org/wiki/Chad%C3%B4
 22413 |  0.8625401 | Cérémonie du thé japonaise — Wikipédia | https://fr.wikipedia.org/wiki/Chanoyu
 13979 |  0.8246348 | Catégorie:Thé au Japon — Wikipédia     | https://fr.wikipedia.org/wiki/Cat%C3%A9gorie:Th%C3%A9_au_Japon
 13023 | 0.81228346 | Thé — Wikipédia                        | https://fr.wikipedia.org/wiki/Th%C3%A9
  1745 | 0.73748296 | Bancha (thé) — Wikipédia               | https://fr.wikipedia.org/wiki/Bancha_(th%C3%A9)
  1730 |  0.7290929 | Cérémonie du thé — Wikipédia           | https://fr.wikipedia.org/wiki/C%C3%A9r%C3%A9monie_du_th%C3%A9
  1736 | 0.71149355 | Sencha — Wikipédia                     | https://fr.wikipedia.org/wiki/Sencha
(10 rows)

On peut voir ainsi que le score de la page intitulée “Catégorie:Thé au Japon” a été légèrement amélioré, ce qui permet à ce document d’être classé avant la page générale “Thé”.

Le poids des mots… sans photo

PostgreSQL permet de donner plus d’importance (plus de poids) aux mots d’une certaine partie d’un document. Cela s’effectue avec la fonction setweight(ts_vector,poids). Le poids est une simple lettre A,B,C ou D (par ordre décroissant). Ce poids figure dans les éléments d’un ts_vector. Pour rappel, voici un ts_vector sans poids :

loxodata_text=# select to_tsvector('french_custom','Vive postgres');
    to_tsvector
--------------------
 'postgr':2 'viv':1
(1 row)

Voici ce même vecteur auquel on a attribué le poids ‘A’ :

loxodata_text=# select setweight(to_tsvector('french_custom','Vive postgres'),'A');
      setweight
----------------------
 'postgr':2A 'viv':1A
(1 row)

Et voici maintenaant la concaténation de deux vecteurs de poids différents :

loxodata_text=# select setweight(to_tsvector('french_custom','Vive postgres'),'A') || setweight(to_tsvector('french_custom','PostgreSQL est un SGBDR'),'B');
              ?column?
------------------------------------
 'postgr':2A,3B 'sgbdr':6B 'viv':1A
(1 row)

On peut donc effectuer notre test précédent en attribuant un poids supérieur au titre du document (on affiche cette fois 12 résultats au lieu de 10) :

loxodata_text=# select * from (select id,ts_rank(setweight(to_tsvector('french_custom',title),'A') ||  setweight(to_tsvector('french_custom',content),'B'),
to_tsquery('french_custom','thé & japonais'))  , title,url
from pages where lang='FR' and  to_tsquery('french_custom','thé & japonais') @@ to_tsvector('french',content) order by 2 desc) foo
limit 12 ;
  id   |  ts_rank   |                 title                  |                                   url
-------+------------+----------------------------------------+-------------------------------------------------------------------------
  1716 | 0.99999994 | Thé au Japon — Wikipédia               | https://fr.wikipedia.org/wiki/Th%C3%A9_au_Japon
 13527 | 0.99999994 | Thé vert — Wikipédia                   | https://fr.wikipedia.org/wiki/Th%C3%A9_vert
 13979 | 0.99999684 | Catégorie:Thé au Japon — Wikipédia     | https://fr.wikipedia.org/wiki/Cat%C3%A9gorie:Th%C3%A9_au_Japon
 22800 |  0.9999663 | Catégorie:Thé japonais — Wikipédia     | https://fr.wikipedia.org/wiki/Cat%C3%A9gorie:Th%C3%A9_japonais
 13056 | 0.99990284 | Cérémonie du thé japonaise — Wikipédia | https://fr.wikipedia.org/wiki/C%C3%A9r%C3%A9monie_du_th%C3%A9_japonaise
 25315 | 0.99990284 | Cérémonie du thé japonaise — Wikipédia | https://fr.wikipedia.org/wiki/Chad%C3%B4
 22413 | 0.99990284 | Cérémonie du thé japonaise — Wikipédia | https://fr.wikipedia.org/wiki/Chanoyu
 13023 | 0.99958086 | Thé — Wikipédia                        | https://fr.wikipedia.org/wiki/Th%C3%A9
  1745 |  0.9980019 | Bancha (thé) — Wikipédia               | https://fr.wikipedia.org/wiki/Bancha_(th%C3%A9)
  1730 |  0.9977101 | Cérémonie du thé — Wikipédia           | https://fr.wikipedia.org/wiki/C%C3%A9r%C3%A9monie_du_th%C3%A9
  1736 |  0.9971023 | Sencha — Wikipédia                     | https://fr.wikipedia.org/wiki/Sencha
 13321 | 0.99643356 | Kamairicha — Wikipédia                 | https://fr.wikipedia.org/wiki/Kamairicha
(12 rows)

Cela à permis de faire apparaître “Catégorie:Thé japonais” en quatrième position, tandis que l’article sur le Sencha est passé à la onzième place.

Extrait pertinent

Plutôt qu’afficher l’URL, il peut être intéressant d’afficher l’extraît de texte le plus pertinent du document. Cela peut se faire avec la fonction ts_headline que nous pouvons appliquer aux 5 premiers résultats de notre requête :

loxodata_text=# select id,rank,title, ts_headline('french_custom',title||' '||content,to_tsquery('french_custom','thé & japonais')) from (select id,ts_rank(setweight(to_tsvector('french_custom',title),'A') ||  setweight(to_tsvector('french_custom',content),'B'),
to_tsquery('french_custom','thé & japonais')) as rank , title,url,content
from pages where lang='FR' and  to_tsquery('french_custom','thé & japonais') @@ to_tsvector('french',content) order by 2 desc) foo
limit 5 ;


  id   |    rank    |                 title                  |                                                                    ts_headline
-------+------------+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------
 13527 | 0.99999994 | Thé vert — Wikipédia                   | <b>Thé</b> au <b>Japon</b>. <b>Thé</b> vert <b>japonais</b> Sencha <b>Thé</b> vert Genmaicha Le <b>Japon</b> produit essentiellement du <b>thé</b>
  1716 | 0.99999994 | Thé au Japon — Wikipédia               | <b>thé</b> au <b>Japon</b>. Le <b>thé</b> de Toganoo est considéré comme le meilleur <b>thé</b> du <b>Japon</b>
 13979 | 0.99999684 | Catégorie:Thé au Japon — Wikipédia     | <b>Thé</b> <b>japonais</b> – 20 PM Maître de <b>thé</b> <b>japonais</b> – 27 P Pages dans la catégorie « <b>Thé</b>
 22800 |  0.9999663 | Catégorie:Thé japonais — Wikipédia     | <b>Thé</b> <b>japonais</b> — Wikipédia Catégorie:<b>Thé</b> <b>japonais</b> — Wikipédia Aller au contenu Menu principal Menu principal déplacer
 13056 | 0.99990284 | Cérémonie du thé japonaise — Wikipédia | <b>thé</b> au <b>Japon</b>, ou « service <b>japonais</b> du <b>thé</b> », appelée chanoyu, ou sadō, ou encore
(5 rows)

La taille du ou des extraits retournés est un paramètre de la fonction ts_headline() de même que les balises de mise en évidence (<b></b>). Attention, cette fonction est coûteuse en CPU, c’est pourquoi il est convenable d’être attentif à ne l’appliquer que sur un resultset déjà réduit au maximum.

Performances

Si vous avez essayé, de votre côté, de reproduire les exemples précédents au fur et à mesure de votre lecture, il est probable que vous éprouviez quelques inquiétudes quant aux performances du FTS. Le problème étant que la fonction to_tsvector() est “lente”. Si l’on souhaite effectuer des recherches avec des performances correctes, il est impératif de précalculer ces vecteurs de lexèmes.

On va donc ajouter une colonne de type tsvector à notre table :

loxodata_text=# alter table pages add column vector tsvector;
ALTER TABLE

Puis nous allons précalculer nos vecteurs conformément à la langue du document. Dans le cas d’une base de données en exploitation, il serait bien sûr beaucoup plus pertinent de mettre un trigger sur la table afin que le champ vector soit renseigné/mis à jour lors des INSERT ou UPDATE :

loxodata_text=# update pages set vector = to_tsvector('french_custom',title || ' ' || content) where lang = 'FR' and content is not null;
UPDATE 8393
Time: 1371518,736 ms (22:51,519)
loxodata_text=# update pages set vector = to_tsvector('english_custom',title || ' ' || content) where lang = 'EN' and content is not null;
UPDATE 6150
Time: 351283,849 ms (05:51,284)

Si nous effectuons notre recherche originale sur le thé japonais avec un EXPLAIN ANALYZE successivement en calculant le vecteur puis en utilisant le champ vector qui vient d’être créé nous avons les résultats suivants :

loxodata_text=# explain analyze select id, title,url from pages where lang='FR' and  to_tsquery('french_custom','thé & japonais') @@ to_tsvector('french_custom',content);
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..169567.06 rows=15 width=99) (actual time=373.272..21921.275 rows=3007 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on pages  (cost=0.00..168565.56 rows=6 width=99) (actual time=322.930..21800.415 rows=1002 loops=3)
         Filter: ((lang = 'FR'::bpchar) AND ('''the'' & ''japon'''::tsquery @@ to_tsvector('french_custom'::regconfig, content)))
         Rows Removed by Filter: 432113
 Planning Time: 0.138 ms
 Execution Time: 21921.710 ms
(8 rows)

Time: 21922,225 ms (00:21,922)

loxodata_text=# explain analyze select id, title,url from pages where lang='FR' and  to_tsquery('french_custom','thé & japonais') @@ vector;
                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..34297.92 rows=810 width=99) (actual time=0.414..479.358 rows=3007 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on pages  (cost=0.00..33216.92 rows=338 width=99) (actual time=0.218..384.590 rows=1002 loops=3)
         Filter: (('''the'' & ''japon'''::tsquery @@ vector) AND (lang = 'FR'::bpchar))
         Rows Removed by Filter: 432113
 Planning Time: 0.158 ms
 Execution Time: 479.523 ms
(8 rows)

Time: 480,046 ms

Pour le moment, laissons de côté le temps d’éxécution qui est beaucoup lié à l’état des caches. On peut voir que le coût estimé par le planner est déjà divisé par 5 (34297.92 vs 169567.06) en utilisant notre champ précalculé.

On aurait pu être tenté de partitionner notre table pages sur la langue, mais l’intérêt serait limité puisqu’on peut également créer des index partiels. Pour rappel, si un tableau est indexé dans un index BTree, l’index permettra de trouver rapidement exactement ce tableau dans son intégralité. Tandis que des index GiN ou GiST permettront de retrouver les tableaux qui contiennent les éléments recherchés.

Nous allons donc indexer les éléments du champ vector pour chaque langue :

loxodata_text=# create index x_pages_vector_fr ON pages USING GIN(vector) where lang='FR';
CREATE INDEX
Time: 10520,209 ms (00:10,520)
loxodata_text=# create index x_pages_vector_en ON pages USING GIN(vector) where lang='EN';
CREATE INDEX
Time: 11435,435 ms (00:11,435)

Le EXPLAIN ANALYZE de la requête de recherche devient ainsi :

loxodata_text=# explain analyze select id, title,url from pages where lang='FR' and  to_tsquery('french_custom','thé & japonais') @@ vector;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on pages  (cost=40.69..2817.94 rows=810 width=99) (actual time=0.446..2.632 rows=3007 loops=1)
   Recheck Cond: (('''the'' & ''japon'''::tsquery @@ vector) AND (lang = 'FR'::bpchar))
   Heap Blocks: exact=606
   ->  Bitmap Index Scan on x_pages_vector_fr  (cost=0.00..40.48 rows=810 width=0) (actual time=0.392..0.392 rows=3007 loops=1)
         Index Cond: (vector @@ '''the'' & ''japon'''::tsquery)
 Planning Time: 0.347 ms
 Execution Time: 2.731 ms
(7 rows)

Time: 3,329 ms

Comme on peut le voir, cet index permet de diviser par douze le coût estimé par le planner.

Le cas de l’index GiST

Si on compare l’exécution avec un index GiST au lieu de GIN nous avons :

loxodata_text=# drop index x_pages_vector_fr;
DROP INDEX
Time: 155,098 ms
loxodata_text=# drop index x_pages_vector_en;
DROP INDEX
Time: 45,920 ms
loxodata_text=# CREATE INDEX x_pages_vector_fr ON pages USING GIST(vector tsvector_ops) WHERE lang='FR';
CREATE INDEX
Time: 1969,569 ms (00:01,970)
loxodata_text=# CREATE INDEX x_pages_vector_en ON pages USING GIST(vector tsvector_ops) WHERE lang='EN';
CREATE INDEX
Time: 1891,561 ms (00:01,892)
loxodata_text=# explain analyze select id, title,url from pages where lang='FR' and  to_tsquery('french_custom','thé & japonais') @@ vector;
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on pages  (cost=36.59..3608.35 rows=1072 width=99) (actual time=1.882..69.167 rows=3007 loops=1)
   Recheck Cond: (('''the'' & ''japon'''::tsquery @@ vector) AND (lang = 'FR'::bpchar))
   Rows Removed by Index Recheck: 1533
   Heap Blocks: exact=750
   ->  Bitmap Index Scan on x_pages_vector_fr  (cost=0.00..36.32 rows=1072 width=0) (actual time=1.724..1.725 rows=4540 loops=1)
         Index Cond: (vector @@ '''the'' & ''japon'''::tsquery)
 Planning Time: 0.199 ms
 Execution Time: 69.357 ms
(8 rows)

On peut voir que le coût estimé est supérieur avec un GiST qu’avec un GIN. Le point le plus intéressant c’est que le scan de l’index GiST a ramené plus de lignes (4540) que celui de l’index GIN lequel a ramené exactement le même nombre de lignes (3007) que la requête elle-même. Ce comportement était prévisible car si le GIN indexe des valeurs exactes, le GiST indexe lui des checksums de valeurs, ce qui entraîne de faux positifs et donc nécessite un filtrage après coup des lignes ramenées par le scan de l’index, et donc des lectures superflues, et donc forcément un coût supplémentaire. L’utilisation d’un index GiST peut cependant parfois se justifier, en particulier parce qu’il supporte l’inclusion de valeurs (CREATE INDEX ... INCLUDE (champ)). Donc si votre table de documents comporte d’autres champs quasi-systématiquement utilisés dans la clause WHERE (identifiant de l’auteur, droits d’accès etc.) l’utilisation du GiST peut être pertinente afin de pouvoir inclure cette ou ces donnée(s) à l’index.

Limites

Il faut savoir que lorsque des ts_vector sont indexés, seuls les lexèmes le sont. Le poids éventuellement associé à chaque lexème ne l’est pas. Cela ne pose en soit pas de problème, mais si vous avez précalculé votre champ vector en concaténant des fragments de texte de poids différents, ne soyez pas surpris d’avoir éventuellement 2 champs vector différents qui, selon l’index sont égaux.

Conclusion

Les capacités de recherches en texte intégral de PostgreSQL sont très largement méconnues, surtout en France, alors que quelques simples efforts de configurations permettraient dans bien des cas d’espèce de s’affranchir de coûteux moteurs spécialisés et propriétaires.

Configuration

Si la configuration par défaut de la recherche en texte intégral de PostgreSQL est assez efficace en anglais, il convient pour le français (ou l’espagnol) de compléter le paramétrage par défaut. Pour cela :

Copier la configuration ‘french’ dans une nouvelle (french_custom par exemple).
Installer l’extension unaccent.
Installer éventuellement l’extension DICT_XSYN.
Supprimer le mapping des éléments que l’on souhaite exclure de la recherche (emails, URLs, valeurs numériques…)
Paramétrer un fichier .stop (non accentué) et attribuer ce fichier au dictionnaire french_stem.
Paramétrer un fichier .syn ou .rules et créer le dictionnaire de synonymes.
Modifier le mapping des éléments lexicographiques asciiword, hword, hword_part, word vers les dictionnaires unaccent, syn_fr, french_stem (unaccent est inutile pour asciiword).

Modélisation

En plus du ou des champ(s) text contenant les textes sur lesquels seront effectués des recherches, il convient d’avoir un champ dans lequel est précalculé le ts_vector. Un trigger pour alimenter ce champ est recommandé.
Si les documents sont suceptibles d’être dans plusieurs langues, un champ identifiant la langue du document est nécessaire.
Un index GIN ou GiST doit être créé sur le ts_vector précalculé. Si la base est mutilingue, il est recommandé de faire un index séparé par langue. L’index GIN est recommandé, sauf s’il est pertinent, en terme de performances, d’inclure dans l’index des données annexes.

Maintenance

Toute modification d’un dictionnaire (ajout de stop-words, de synonymes etc. ) ou modification de la configuration TEXT SEARCH impose évidemment de lancer un recalcul des champs ts_vector précalculés.
La modification d’un dictionnaire peut être prise en compte immédiatement à l’aide d’un ALTER. Par exemple, dans le cas de la modification des stop-words :

ALTER TEXT SEARCH DICTIONARY french_stem(STOPWORDS = custom_french );

Volumétries

Pour rappel, j’ai constitué pour rédiger cet article une base contenant 14.543 articles de wikipédia. Cela représente 493 Mo de texte brut :

loxodata_text=# select pg_size_pretty(sum(octet_length(content))) from pages;
 pg_size_pretty
----------------
 493 MB

Grace au mécanisme de compression intégré à pg_toast, ces textes n’occupent que 264 Mo dans la base :

loxodata_text=# select pg_size_pretty(sum(pg_column_size(content))) from pages;
 pg_size_pretty
----------------
 264 MB

Notez que j’ai utilisé la compression transparente par défaut pour les champs TOASTed PGLZ (champs TEXT et TS_VECTOR). Depuis la version 14 de PostgreSQL, la compression LZ4 est disponible, paramétrable champ par champ si besoin, laquelle permet une amélioration significative des performances au détriment d’une perte de compression infinitésimale. N’oubliez pas d’explorer ce point dans votre tuning.

Le champ ts_vector précalculé occupe 294 Mo :

loxodata_text=# select pg_size_pretty(sum(pg_column_size(vector))) from pages;
 pg_size_pretty
----------------
 294 MB

Les index GIN occupent 122 Mo :

loxodata_text=# select pg_size_pretty(pg_relation_size('x_pages_vector_en') + pg_relation_size('x_pages_vector_fr') );
 pg_size_pretty
----------------
 122 MB

Il semble donc raisonnable de prévoir au moins 1,4 Mo d’espace de stockage pour chaque Mo de texte brut dans le cadre d’une base conçue pour la recherche en texte intégral.

LOXODATA