Le blog de Jean David TECHER, un Réunionnais à Saint-Priest/Lyon

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

Suite à un billet précédent, je propose donc ce petit test pour permettre de pouvoir réduire une image!

Tout d'abord il nous faut convert (fournit dans le paquet imagemagick d'ubuntu) et imagesize (fournit quand à lui dans le paquet libimagesize-perl)

apt-get install libimage-size-perl imagemagick 

Supposons que j'ai une image Capture-1.png dont j'obtiens les dimensions par

root@bremko:/home/david# imgsize Desktop/Capture-1.png
width="1280" height="800"

L'option -r de imgsize (cf. man imgsize) me permet de me débarasser des witdh et heights

root@bremko#imgsize -r Desktop/Capture-1.png
1280 800

La page man de convert me permet de savoir que pour réduire une image il suffit d'utiliser l'option resize

convert -resize [nouveau_width]x[nouveau_height] [image_source] [image_finale]

Donc pour mon exemple, en utilsant cut pour récupérer à la fois width et height depuis imgsize, ainsi que expr je peux par exemple faire un petit script comme celui-ci pour réduire mon image par exemple d'un rapport au quart

#!/bin/sh
 image="/home/david/Desktop/Capture-1.png"
rapport=4 
width=$(imgsize -r ${image} |cut -d ' ' -f 1)
height=$(imgsize -r ${image} |cut -d ' ' -f 2)
convert -resize $(expr ${width} / ${rapport})x$(expr ${height} / ${rapport}) ${image} ${image}

Suite à l'exécution de ce script par sh script.sh, imgsize me confirme bien les nouvelles dimensions qui sont donc

root@bremko:/home/david# imgsize Desktop/Capture-1.png
width="320" height="200"

GDAL a donc sortie sa version 1.4.0 récemment! L'un des nouveautés qui m'intéresse surtout c'est la possibilité pour le format KML avec ogr2ogr! Oh yeahh ! Apparement cette nouvelle mouture supporte aussi l'utilisation des schémas pour OGR mais pas encore essayé!

Vous pouvez voir les nouveautés de la 1.4.0 à cette adresse http://www.gdal.org/NEWS.html

A ça c'est un truc de geek! Ca me sert surtout ici pour mettre mes photos, screenshots sur le blog! C'est sur le channel irc de Ubuntu irc.freenode.net/canal:ubuntu-fr que j'ai trouvé l'astuce

apt-get install libimage-size-perl

et pour l'utiliser

imgsize -r [image]

Remerciements: Merci à McPeter du chan' pour m'avoir donner l'info sur ce truc