Introduction
Tsearch est une extensions utilisable avec PostgreSQL pour la recherche de textes dans les colonnes d'une base de données, comme pour les moteurs de recherche, comme google, yahoo etc... Pour une meilleure présentation de Tsearch, merci de consultez le lien http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
Nous allons ici essayer de l'utiliser sur une table contenant pas moins de ??? enregistrements. Les tests ici ont lieu avec PostgreSQL 8.1.5
Pré-requis
Avoir PostgreSQL d'installé!
Installation
Les sources de tsearch se trouvent dans le sous-répertoire contrib des sources de PostgreSQL. Pour l'installation, il suffira de faire en tant que root
cd contrib/tsearch2
make
make install
Pour charger les fonctionnalités de tsearch dans notre base - que nous appelerons ici testdb -, il suffira de faire
su postgres
psql -d testdb -f /usr/local/pgsql/share/contrib/tsearch2.sql
Dans notre base, nous avons une table bdnyme dont un des champs est nom dont voici un extrait
testdb#select distinct nom from bdnyme where like(nom,'%Castelnau%') limit 4;
nom
---------------------------------------
Aérodrome de Castelnaudary-Villeneuve
Aérodrome de Castelnau-Magnoac
Barrage de Castelnau-Lassouts
Bois Communal de Castelnau-de-Médoc
(4 lignes)
ALTER TABLE public.bdnyme ADD COLUMN nom_vectors tsvector;
UPDATE public.bdnyme SET nom_vectors=to_tsvector('simple', nom);
VACUUM FULL ANALYZE;
CREATE INDEX nom_idxv ON public.bdnyme USING gist(nom_vectors);
VACUUM FULL ANALYZE;
Je tiens quand même à préciser ici que les lignes UPDATE et CREATE INDEX m'ont pris quand même un sacré temps pour une table de plus de 1.4 millions de lignes! Au moins entre 9h00 à 12h00 sur mon pauvre SONY VAIO FS315H. Mais bon je n'ai pas de grosse machine à la maison
Pour le fun, par exemple recherchons par exemple le caractère 'castelnau' sans tenir compte de la casse dans la colonne nom de ma tabe
testdb=# explain analyze select nom from bdnyme where nom ~* 'castelnau';
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on bdnyme (cost=0.00..85066.32 rows=4 width=16) (actual time=600.210..18545.878 rows=51 loops=1)
Filter: ((nom)::text ~* 'castelnau'::text)
Total runtime: 18546.257 ms
(3 lignes)
Soit 18 secondes mama 
!!!
Bon au tour de Tsearch maintenant 
!
testdb=# explain analyze select nom from bdnyme where nom_vectors @@ to_tsquery('simple','castelnau');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on bdnyme (cost=39.74..5164.24 rows=1449 width=16) (actual time=38.356..38.739 rows=49 loops=1)
Filter: (nom_vectors @@ '''castelnau'''::tsquery)
-> Bitmap Index Scan on nom_idxv (cost=0.00..39.38 rows=1449 width=0) (actual time=38.299..38.299 rows=49 loops=1)
Index Cond: (nom_vectors @@ '''castelnau'''::tsquery)
Total runtime: 39.047 ms
(5 lignes)
Soit 0.03 secondes. Purée 
...... ! Requête de l'enfer de la mort qui du le diable 
!
Promettant comme on dit! Bon je n'ai pas encore essayé les index GIN à la place de GiST pour le moment bien que la rumeur veuille que leur création prennent beaucoup de temps! Mais bon à tester, à tester!
Utilisation du dictionnaire fançais
Pour utiliser le dictionnaire, on fera
BEGIN TRANSACTION;
INSERT INTO pg_ts_cfg (ts_name, prs_name, locale) VALUES ('default_french', 'default', 'fr_FR.UTF-8');
insert into pg_ts_cfgmap values ('default_french','email','{simple}');
insert into pg_ts_cfgmap values ('default_french','file','{simple}');
insert into pg_ts_cfgmap values ('default_french','float','{simple}');
insert into pg_ts_cfgmap values ('default_french','host','{simple}');
insert into pg_ts_cfgmap values ('default_french','hword','{simple}');
insert into pg_ts_cfgmap values ('default_french','int','{simple}');
insert into pg_ts_cfgmap values ('default_french','lhword','{fr_ispell}');
insert into pg_ts_cfgmap values ('default_french','lpart_hword','{fr_ispell}');
insert into pg_ts_cfgmap values ('default_french','nlhword','{simple}');
insert into pg_ts_cfgmap values ('default_french','nlpart_hword','{simple}');
insert into pg_ts_cfgmap values ('default_french','part_hword','{simple}');
insert into pg_ts_cfgmap values ('default_french','sfloat','{simple}');
insert into pg_ts_cfgmap values ('default_french','uint','{simple}');
insert into pg_ts_cfgmap values ('default_french','uri','{simple}');
insert into pg_ts_cfgmap values ('default_french','url','{simple}');
insert into pg_ts_cfgmap values ('default_french','version','{simple}');
insert into pg_ts_cfgmap values ('default_french','word','{fr_ispell}');
insert into pg_ts_cfgmap values ('default_french','nlword','{fr_ispell}');
insert into pg_ts_cfgmap values ('default_french','lword','{fr_ispell,simple}');
INSERT INTO pg_ts_dict
(SELECT 'fr_ispell',
dict_init,
'DictFile="/home/david/download/ispell-french/french.dict",'
'AffFile="/home/david/download/ispell-french/french.aff",'
'StopFile="/home/postgres/french.stop"',
dict_lexize
FROM pg_ts_dict
WHERE dict_name = 'ispell_template');
END TRANSACTION;
On peut vérifier tout ça en faisant par exemple
ignportal=# select to_tsvector('default_french','mon village écolé');
to_tsvector
---------------------
'mon':1 'village':2
(1 ligne)
ignportal=# select to_tsvector('default_french','mon village école');
to_tsvector
-------------------------------
'mon':1 'école':3 'village':2
(1 ligne)
ignportal=# select to_tsvector('default_french','mon village ecole');
to_tsvector
-------------------------------
'mon':1 'ecole':3 'village':2
(1 ligne)
ignportal=# select to_tsvector('default_french','mon village ecole d\'avant là guerre');
to_tsvector
-----------------------------------------------------------------
'd':4 'là ':6 'mon':1 'avant':5 'ecole':3 'guerre':7 'village':2
(1 ligne)
ignportal=# select to_tsvector('default_french','mon village école d\'avant là guerre');
to_tsvector
-----------------------------------------------------------------
'd':4 'là ':6 'mon':1 'avant':5 'guerre':7 'école':3 'village':2
(1 ligne)
ignportal=# select to_tsvector('default_french','mon village écolu d\'avant là guerre');
to_tsvector
-------------------------------------------------------
'd':3 'là ':5 'mon':1 'avant':4 'guerre':6 'village':2
(1 ligne)
ignportal=# select to_tsvector('default_french','mon village écoles d\'avant là guerre');
to_tsvector
-----------------------------------------------------------------
'd':4 'là ':6 'mon':1 'avant':5 'guerre':7 'école':3 'village':2
(1 ligne)
ignportal=# select to_tsvector('default_french','mon village écol d\'avant là guerre');
to_tsvector
-------------------------------------------------------
'd':3 'là ':5 'mon':1 'avant':4 'guerre':6 'village':2
(1 ligne)
ignportal=# select to_tsvector('default_french','mon village écolier d\'avant là guerre');
to_tsvector
-------------------------------------------------------------------
'd':4 'là ':6 'mon':1 'avant':5 'guerre':7 'village':2 'écolier':3
(1 ligne)
ignportal=# select to_tsvector('default_french','mon village écoliers d\'avant là guerre');
to_tsvector
-------------------------------------------------------------------
'd':4 'là ':6 'mon':1 'avant':5 'guerre':7 'village':2 'écolier':3
(1 ligne)
ignportal=# select to_tsvector('default_french','mon village écolières d\'avant là guerre');
to_tsvector
-------------------------------------------------------
'd':3 'là ':5 'mon':1 'avant':4 'guerre':6 'village':2
(1 ligne)
ignportal=# select to_tsvector('default_french','mon village écoliers d\'avant là guerre');
to_tsvector
-------------------------------------------------------------------
'd':4 'là ':6 'mon':1 'avant':5 'guerre':7 'village':2 'écolier':3
(1 ligne)
ignportal=# select to_tsvector('default_french','ils sont beaux les nouveaux écoliers dans leur nouvelles école');
to_tsvector
------------------------------------------------------------------------------------------------------------
'il':1 'le':1,4 'dan':7 'les':4 'beau':3 'leur':8 'sont':2 'école':10 'nouveau':5 'nouvelle':9 'écolier':6
(1 ligne)
ignportal=# select to_tsvector('default_french','ils sont beaux les nouveaux écoliers dans leur nouvelles école mais école prend un accent');
to_tsvector
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'ce':15 'il':1 'le':1,4 'un':14 'dan':7 'les':4 'mai':11 'beau':3 'cens':15 'leur':8 'mais':11 'sont':2 'accent':15 'prends':13 'école':10,12 'nouveau':5 'nouvelle':9 'écolier':6
(1 ligne)
ignportal=# select to_tsvector('default_french','ils sont beaux les nouveaux écoliers dans leur nouvelles école mais école prend un accent, pas ecole!');
to_tsvector
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'ce':15 'il':1 'le':1,4 'un':14 'dan':7 'les':4 'mai':11 'pas':16 'beau':3 'cens':15 'leur':8 'mais':11 'sont':2 'ecole':17 'accent':15 'prends':13 'école':10,12 'nouveau':5 'nouvelle':9 'écolier':6
(1 ligne)
ignportal=# select to_tsvector('default_french','ils sont beaux les nouveaux écoliers dans leur nouvelles école de Castelnau-Le-Lez mais école prend un accent, pas ecole!');
to_tsvector
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'ce':18 'de':11 'il':1 'le':1,4,12 'un':17 'dan':7 'les':4 'lez':13 'mai':14 'pas':19 'beau':3 'cens':18 'leur':8 'mais':14 'sont':2 'ecole':20 'accent':18 'prends':16 'école':10,15 'nouveau':5 'nouvelle':9 'écolier':6
Maintenant on remet tout en place
ignportal=# drop INDEX nom_idxv ;
DROP INDEX
ignportal=# alter TABLE bdnyme drop COLUMN nom_vectors;
ALTER TABLE
ignportal=# ALTER TABLE public.bdnyme ADD COLUMN nom_vectors tsvector;
ALTER TABLE
ignportal=# explain ANALYZE UPDATE public.bdnyme SET nom_vectors=to_tsvector('default_french', nom);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on bdnyme (cost=0.00..49545.33 rows=1449466 width=95) (actual time=15.996..215115.297 rows=1449466 loops=1)
Total runtime: 20318651.206 ms
(2 lignes)
Soit déjà 5h38m38s pour cette dernière requête
Maintenant un petit Vacuum sur la table
VACUUM FULL ANALYZE
Puis on crée l'index
postgres@bremko:/home/david$ time psql ignportal -c "CREATE INDEX nom_idxv ON public.bdnyme USING gist(nom_vectors)"
CREATE INDEX
real 4m44.361s
user 0m0.024s
sys 0m0.000s
on va essayer!
!
Samedi dernier, ca a été sorti au cinéma, histoire de se changer les idées pour avoir taffer comme il faut toute la semaine 
! Donc la séance de 22h15, j'ai débarqué là -bas vers 20h00. J'ai pris mon billet. J'suis allé ensuite me prendre un petit martini (ça me fait penser à Djay 
) au 3 Brasseurs! Puis j'suis allé manzé au resto chinois juste derrière! Mama je me suis goinfré comme pas possible! Une bonne petite demi-heure de digestion avec un café avec une clope puis ça a été direction le film!