Annexe E. PostgreSQL et les index
Précédent Annexes Suivant

Annexe E. PostgreSQL et les index

Table des matières

E.1. Importation d'un jeu de données
E.2. Importer des données au format CSV dans PostgreSQL
E.3. Index B-tree, opérateur =
E.4. Index B-tree, fonctions
E.5. Index B-tree, recherche sur motif, like "chaine%"
E.6. Index B-tree, recherche sur motif, like "%chaine"
E.6.1. Fonction inversant une chaîne en PostgreSQL
E.6.2. Création de l'index sur le champs "Commune" de la table insee
E.6.3. Requêtes

Dans cet article, nous allons voir quelques exemples d'utilisation de PostgreSQL avec les index.

Ici je ne discute pas de l'intérêt des index sur des tables ou quand on doit y recourir pour optimiser des plans de requêtes. Mon but avant tout est de rappeler des exemples d'utilisation des index avec PostgreSQL. En premier lieu, je ne saurais vous conseiller de vous accorder dans votre temps libre un moment pour consulter le chapitre 11 de la documentation française http://docs.postgresqlfr.org/8.2/indexes.html

E.1. Importation d'un jeu de données

Nous allons ici commencer par importer le jeu de données provenant de l'URL suivante http://www.galichon.com/codesgeo/data/insee.zip ensuite on le décompresse

$wget http://www.galichon.com/codesgeo/data/insee.zip
$unzip insee.zip

Ce zip contient un fichier CSV réalisé par Jérôme GALICHON

E.2. Importer des données au format CSV dans PostgreSQL

En faisant un petit less insee.csv, on voit que les données doivent être importées dans une table dont les champs sont

#less insee.csv
Commune;Codepos;Departement;INSEE

Pour importer les données, il faut dont commencer pas créer une tables que nous appellerons insee en respectant la casse

igo=# CREATE TABLE insee("Commune" text,"Codepos" varchar(5),"Departement" text,"INSEE" int4);

Comme sur la première ligne du fichier, il y a les noms des colonnes, il nous faut utiliser la commande COPY FROM en spécifiant l'option HEADER:

igo#COPY insee FROM '/home/david/download/insee.csv' DELIMITERS ';' CSV HEADER;

Vérifions que nous avons une table pleinne

igo=# SELECT * FROM insee LIMIT 5;
         Commune         | Codepos | Departement | INSEE
-------------------------+---------+-------------+-------
 L ABERGEMENT CLEMENCIAT | 01400   | AIN         |  1001
 L ABERGEMENT DE VAREY   | 01640   | AIN         |  1002
 AMAREINS                | 01090   | AIN         |  1003
 AMBERIEU EN BUGEY       | 01500   | AIN         |  1004
 AMBERIEUX EN DOMBES     | 01330   | AIN         |  1005
(5 lignes)

E.3. Index B-tree, opérateur =

Les index B-tree sont les index classiques avec PostgreSQL. Suppons pour commencer ce que celà donne sans utiliser d'index si on veut connaître la commune dont le code postal est 34170

igo=# SELECt "Commune" FROM insee WHERE "Codepos"='34170';
     Commune
------------------
 CASTELNAU LE LEZ
(1 ligne)

En utilisant EXPLAIN ALAYZE, j'ai une premier plan de requête

igo=# EXPLAIN ANALYZE SELECT "Commune" FROM insee WHERE "Codepos"='34170';
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Seq Scan on insee  (cost=0.00..680.40 rows=121 width=32) (actual timlme=6.821..25.082 rows=1 loops=1)
   Filter: (("Codepos")::text = '34170'::text)
 Total runtime: 25.135 ms
(3 lignes)

Seq Scan on insee m'indique que la requête a exigé un parcours séquentiel sur les lignes du tableau. Je n'ai que 36000 en tous. Voyons si un index peut nous aider à améliorer tout ça

Ici bien sûr, je vous renvois à la documentation de PostgreSQL citée en début d'article.

igo=# CREATE INDEX codepos_idx on insee("Codepos");
CREATE INDEX
igo=# VACUUM FULL ANALYZE ;

Refaisons la requête à nouveau

igo=# EXPLAIN ANALYZE SELECT "Commune" FROM insee WHERE "Codepos"='34170';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Scan using codepos_idx on insee  (cost=0.00..8.55 rows=11 width=15) (actual time=0.033..0.039 rows=1 loops=1)
   Index Cond: (("Codepos")::text = '34170'::text)
 Total runtime: 0.097 ms
(3 lignes)

Déjà je note un temps d'éxécution significatif en ayant recours à un index!

E.4. Index B-tree, fonctions

Dans ma table insee, les noms des communes sont capitalisées. Or lower() est une fonction de PostgreSQL qui permet sur une chaines de caractères de la renvoyer en minuscule.

igo=# explain analyze select * FROM insee WHERE lower("Commune")='castelnau de levis';
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Seq Scan on insee  (cost=0.00..962.24 rows=195 width=40) (actual time=114.664..121.935 rows=1 loops=1)
   Filter: (lower("Commune") = 'castelnau de levis'::text)
 Total runtime: 122.141 ms
(3 lignes)

On peut aussi créer des index sur des fonctions

igo=# CREATE INDEX communes_idx on insee(lower("Commune"));
CREATE INDEX
igo=# VACUUM FULL ANALYZE ;
VACUUM
igo=# explain analyze select * FROM insee WHERE lower("Commune")='castelnau de levis';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Scan using communes_idx on insee  (cost=0.00..8.28 rows=1 width=40) (actual time=0.076..0.082 rows=1 loops=1)
   Index Cond: (lower("Commune") = 'castelnau de levis'::text)
 Total runtime: 0.136 ms
(3 lignes)

igo=#

E.5. Index B-tree, recherche sur motif, like "chaine%"

Ici par rapport à la section précédente, nous allons essayer d'inverser la tendance.

Avec PostgreSQL, il n'est pas possible d'utiliser d'index pour la recherche de motif pour LIKE '%chaine%'. En revanche, il est tout à fait possible de la faire pour un motif de type LIKE 'chaine%'. A condition bien sûr, de prendre en compte la local. Consultez donc http://docs.postgresqlfr.org/pgsql-8.2.1-fr/charset.html

Voyons celà en détail. Chez moi, ma LOCALE n'est pas en 'C'

$locale
LANG=fr_FR.UTF-8
LANGUAGE=fr_FR:fr:en_GB:en
LC_CTYPE="fr_FR.UTF-8"
LC_NUMERIC="fr_FR.UTF-8"
LC_TIME="fr_FR.UTF-8"
LC_COLLATE="fr_FR.UTF-8"
LC_MONETARY="fr_FR.UTF-8"
LC_MESSAGES="fr_FR.UTF-8"
LC_PAPER="fr_FR.UTF-8"
LC_NAME="fr_FR.UTF-8"
LC_ADDRESS="fr_FR.UTF-8"
LC_TELEPHONE="fr_FR.UTF-8"
LC_MEASUREMENT="fr_FR.UTF-8"
LC_IDENTIFICATION="fr_FR.UTF-8"
LC_ALL=

Donc si je veux utiliser la recherche de motif "LIKE 'chaine%', sur le champ Commune de ma table insee, il faut que je fasse

igo=# explain analyze select * FROM insee WHERE "Commune" LIKE 'CASTELNAU%';
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on insee  (cost=0.00..864.86 rows=1 width=40) (actual time=1.331..24.396 rows=27 loops=1)
   Filter: ("Commune" ~~ 'CASTELNAU%'::text)
 Total runtime: 24.568 ms
(3 lignes)

Comme mon champs Communes est de type text, il me suffit de faire

igo=# CREATE INDEX communes_idxx ON insee("Commune" text_pattern_ops);
CREATE INDEX
igo=# VACUUM FULL ANALYZE ;
VACUUM
igo=# explain analyze select * FROM insee WHERE "Commune" LIKE 'CASTELNAU%';
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using communes_idxx on insee  (cost=0.00..8.27 rows=1 width=40) (actual time=0.174..0.352 rows=27 loops=1)
   Index Cond: (("Commune" ~>=~ 'CASTELNAU'::text) AND ("Commune" ~<~ 'CASTELNAV'::text))
   Filter: ("Commune" ~~ 'CASTELNAU%'::text)
 Total runtime: 0.525 ms
(4 lignes)

E.6. Index B-tree, recherche sur motif, like "%chaine"

L'idée ici est de se baser de ce que nous avons appris dans la sous-section précédente. Ici par rapport à la section précédente, il nous faut une petite subtilité. Cette dernière va consister par commencer par la création d'une fonction

E.6.1. Fonction inversant une chaîne en PostgreSQL

On pourra par exemple utiliser la fonction suivante

CREATE OR REPLACE FUNCTION "public"."inverserchaine" (chaine text) RETURNS text AS
$body$
/*
   Inverse la chaine

  Exempe : SELECT inverserchaine('postgresql');
           lqsergtsop
*/
DECLARE
 i integer;
 resultat text = '';
BEGIN
 FOR i IN REVERSE length(chaine)..1 LOOP
  resultat:=resultat||substring(chaine from i for 1);
 END LOOP;
 return resultat;
END;
$body$
LANGUAGE 'plpgsql'  RETURNS NULL ON NULL INPUT SECURITY INVOKER IMMUTABLE;

Cette fonction a été écrite par Damien Griessinger et elle est disponible à http://dgriessinger.developpez.com/postgresql/udf/?page=chaines#inverserchaine . En vertu de la documentation de PostgreSQL à http://traduc.postgresqlfr.org/pgsql-8.2.1-fr/sql-createindex.html

Toutes les fonctions et opérateurs utilisés dans la définition d'index doivent être « immutable » (NDT : immuable), c'est-à-dire que leur résultat ne doit dépendre que de leurs arguments et jamais d'une influence externe (telle que le contenu d'une autre table ou l'heure). Cette restriction permet de s'assurer que le comportement de l'index est strictement défini. Pour utiliser une fonction utilisateur dans une expression d'index ou dans une clause WHERE, cette fonction doit être marquée immutable lors de sa création.

J'ai donc supprimé la spécificité VOLATILE dans la fonction.

E.6.2. Création de l'index sur le champs "Commune" de la table insee

L'index sera créé en faisant

CREATE INDEX communes_idxxx ON insee(inverserchaine("Commune") text_pattern_ops);

Puis on fera tout simplement un

VACUUM FULL ANALYZE;

E.6.3. Requêtes

La requête par exemple pour trouver toutes les communes qui se terminent par "lez" par exemple sera

igo=# SELECT "Commune" FROM insee WHERE inverserchaine("Commune") LIKE inverserchaine('LEZ')||'%';
       Commune
---------------------
 LES BORDES SUR LEZ
 CAMLEZ
 VIEUX VILLEZ
 TREFLEZ
 LEZ
 CASTELNAU LE LEZ
 MONTFERRIER SUR LEZ
 PRADES LE LEZ
 GALEZ
 LIMETZ VILLEZ
 PORT VILLEZ
(11 lignes)

Maintenant si on fait des comparaison avec EXPLAIN ANLYZE

igo=# EXPLAIN ANALYZE SELECT "Commune" FROM insee WHERE inverserchaine("Commune") LIKE inverserchaine('LEZ')||'%';
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on insee  (cost=4.28..11.88 rows=2 width=15) (actual time=0.146..0.579 rows=11 loops=1)
   Filter: (inverserchaine("Commune") ~~ 'ZEL%'::text)
   ->  Bitmap Index Scan on communes_idxxx  (cost=0.00..4.28 rows=2 width=0) (actual time=0.052..0.052 rows=11 loops=1)
         Index Cond: ((inverserchaine("Commune") ~>=~ 'ZEL'::text) AND (inverserchaine("Commune") ~<~ 'ZEM'::text))
 Total runtime: 0.697 ms
(5 lignes)

igo=# EXPLAIN ANALYZE SELECT "Commune" FROM insee WHERE "Commune" LIKE '%LEZ';
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on insee  (cost=0.00..864.86 rows=309 width=15) (actual time=4.559..68.744 rows=11 loops=1)
   Filter: ("Commune" ~~ '%LEZ'::text)
 Total runtime: 68.857 ms
(3 lignes)
Précédent Niveau supérieur Suivant
Annexe D. Pouvoir faire des sauvegardes des bases locales ou distantes d'un serveur PostgreSQL du réseau intranet vers une machine-cliente Sommaire Annexe F. Connaître l'espace disque occupé par les données (dbsize)