Bonnes pratiques

et optimisation

Version 1.4 Ministère de la Transition Ecologique

Licence ETALAB

Janvier 2022
 Table des
matières

Objectifs 5

Introduction 7

I - Installation du serveur PostgreSQL/PostGIS 9

II - Paramétrage du serveur PostgreSQL pour l'exploitation des

données spatiales 13

III - Bonnes pratiques de sécurité 19

IV - Optimisation de la gestion de la base de données 21

V - Optimisation des requêtes SQL 25

Contenus annexes 27

3
Objectifs

Les objectifs du module sont de :

 Savoir installer correctement le serveur PostgreSQL/PostGIS
 Optimiser le paramétrage du serveur pour l'exploitation des
données spatiales
 Connaître et appliquer les bonnes pratiques de sécurité
(intrusion, intégrité des données)
 Savoir optimiser la gestion de la base de données spatiale
 Savoir optimiser les requêtes SQL

5
 Introduction

Le temps d'apprentissage de ce module est estimé à 2 heures et 30 minutes.

Il ne comporte pas d'exercices mais fournit des informations importantes sur l'optimisation et
la sécurité.

7
 Installation du
I -
I
serveur
PostgreSQL/Post
GIS

Fondamental
 Le paramétrage du serveur PostgreSQL est enregistré dans le fichier de
configuration [Link]
 Seul l'administrateur du serveur peut changer les paramètres du serveur et
modifier le fichier [Link]
 Le serveur PostgreSQL doit être relancé pour appliquer les modifications de
configuration
nb : pour le MTES se reporter aux conseils pour le déploiement de l'offre Eole
PostgreSQL1

1 - Choisir un port différent de celui par défaut (5432)

Cette mesure de sécurité basique est surtout conseillée dans le cadre d'une
ouverture vers le web.

Méthode
Dans le fichier de configuration [Link], changer la valeur port=5432

1 - [Link]

9
 Installation du serveur PostgreSQL/PostGIS

2 - Créer un tablespace sur une autre partition que la partition

système

Le tablespace est un espace de stockage qui va permettre de stocker les données

d'une base de données (tables, index, tables systèmes...).
En le définissant sur une autre partition que la partition système, vous évitez
d'encombrer celle-ci en l'attribuant aux nouvelles bases de données.

Attention : le modifier à posteriori pour une base ne sera pris en compte que pour
les nouveaux éléments (tables, index...) de la base.
Pour changer le tablespace sur ceux-ci, il vous faut modifier le tablespace de chacun
des éléments (table, index).

Exemple : Créer un tablespace sur une autre partition que la partition système :
 Sous LINUX : CREATE TABLESPACE espace_rapide LOCATION
'/mnt/sda1/postgresql/data';
 Sous WINDOWS : CREATE TABLESPACE espace_rapide LOCATION
'E:/sda1/postgresql/data';
Remarque :
On peut aussi modifier le paramètre default_tablespace dans le fichier
[Link]
Ex : default_tablespace = '/mnt/sda1/postgresql/data'

10
 Installation du serveur PostgreSQL/PostGIS

3 - Mettre toutes les données dans un ou plusieurs autre(s)

schéma(s) que le schéma par défaut "public"

Cette action très importante est à mettre en œuvre dès la phase de conception de la
base de données
Elle permet de :
 Séparer le coeur de PostGIS (tables systèmes PostGIS, fonctions...) des
données elles-mêmes
 Organiser les données par la création de schémas par thématique métier , ou
selon d'autres organisations (référentiel, données tabulaires/vectorielles etc)
 Simplifier potentiellement la gestion des droits d'utilisation par cette
organisation en schémas multiples
Le principal avantage reste néanmoins de faciliter la mise à jour de PostGIS ainsi que
la restauration et l'échange plus efficaces de données.
L'import d'une sauvegarde contenant les fonctionnalités PostGIS dans une autre
installation peut en effet causer des conflits.

4 - Bien vérifier les "ouvertures" vers l'extérieur

En effet, il est important de limiter l'accès à PostgreSQL au strict nécessaire.

Paramètr Fichier de Commentaires
e configuration

listen_ad [Link] Permet de définir quelles IP sont "écoutées" par

dresses PostgreSQL
(toutes, localhost). Par défaut : '*' (le serveur
écoute tout)

listen_ad pg_hba.conf Permet de gérer plus finement les accès en

dresses configurant les IP ou plages d'IPs autorisées en
précisant les bases autorisées et les modes
d'accès (sans mot de passe, mot de passe
cryptés...).

Le chapitre suivant est consacré à l'optimisation des performances du serveur

PostgreSQL pour l'exploitation des données spatiales.

11
Paramétrage du
II -
II
serveur
PostgreSQL pour
l'exploitation des
données spatiales

Fondamental
 Le paramétrage du serveur PostgreSQL est enregistré dans 2 fichiers de
configuration dont le fichier [Link]
 Seul l'administrateur du serveur peut changer les paramètres du serveur et
modifier le fichier [Link]
 Le serveur PostgreSQL doit être relancé pour appliquer les modifications de
configuration

Mode opératoire
PostgreSQL est une base de données capable de fonctionner dans des
environnements ayant des ressources très limitées et partageant ces ressources avec
un grand nombre d'autres applications.
Afin d'assurer que PostgreSQL tournera convenablement dans ces environnements, la
configuration par défaut est très peu consommatrice de ressources mais inadaptée
pour des bases de données spatiales.
Nous allons voir comment configurer le serveur PostgreSQL pour optimiser son
fonctionnement pour l'exploitation des données spatiales en modifiant quelques
paramètres importants dans le fichier [Link].

Le fichier [Link] peut être édité et modifié par l'administrateur du serveur

dans un éditeur de texte.
Il est également possible, si on dispose des droits de SUPERUSER, d'utiliser les

13
 Paramétrage du serveur PostgreSQL pour l'exploitation des
données spatiales

commandes ALTER SYSTEM2

Paramètres à modifier
Important :
Les valeurs recommandées ci-dessous pour chaque paramètre de configuration
constituent un bon point de départ pour améliorer sensiblement le fonctionnement du
serveur pour l'exploitation des données spatiales, mais elles doivent être testées et
ajustées pour chaque configuration matérielle du serveur informatique, notamment
par rapport à la mémoire vive disponible.

Consommation des ressources : mémoire

shared_buffers (integer)
Initialise la quantité de mémoire que le serveur de bases de données utilise comme
mémoire partagée.
La valeur par défaut, en général 128kB, est insuffisante pour une base de données
spatiales en production.
Si vous disposez d'un serveur dédié à la base de données, avec 1 Go de mémoire ou
plus, une valeur de départ raisonnable pour ce paramètre est de 25% de la mémoire
du système.
PostgreSQL profite aussi du cache du système d'exploitation, il est donc peu probable
qu'une allocation de plus de 40% de la mémoire fonctionnera mieux qu'une valeur
plus restreinte.
Valeur recommandée : 512MB (en fait 1/4 de la mémoire totale de la machine)

work_mem (integer)
Indique la quantité de mémoire que les opérations de tri interne et les tables de
hachage (relations données/empreintes ou signatures des données) peuvent utiliser
avant de basculer sur des fichiers disque temporaires.
La valeur par défaut est de 1MB.
Pour une requête complexe, il peut y avoir plusieurs opérations de tri ou de hachage
exécutées en parallèle ; chacune peut utiliser de la mémoire à hauteur de cette
valeur avant de commencer à placer les données dans des fichiers temporaires sur le
disque.
De plus, de nombreuses sessions peuvent exécuter de telles opérations
simultanément. La mémoire totale utilisée peut, de ce fait, atteindre plusieurs fois la
valeur de work_mem.

2 - [Link]

14
 Paramétrage du serveur PostgreSQL pour l'exploitation
des données spatiales

Il faut considérer combien de connexions et quelle complexité est attendue dans les
requêtes avant d'augmenter cette valeur.
Le bénéfice acquis par l'augmentation de cette valeur est que la plupart des
opérations de classification, dont les clauses ORDER BY et DISTINCT, les jointures,
les agrégations basées sur les hachages et l'exécution de requête imbriquées,
pourront être réalisées sans avoir à passer par un stockage sur disque.
Valeur recommandée : 16MB (on peut mettre plus, par exemple 128MB pour une
mémoire totale de 24 Go RAM)

maintenance_work_mem (integer)
Indique la quantité maximale de mémoire que peuvent utiliser les opérations de
maintenance telles que VACUUM, CREATE INDEX et ALTER TABLE ADD FOREIGN KEY.
La valeur par défaut est de 16MB.
Puisque seule une de ces opérations peut être exécutée à la fois dans une session et
que, dans le cadre d'un fonctionnement normal, peu d'opérations de ce genre sont
exécutées concurrentiellement sur une même installation, il est possible d'initialiser
cette variable à une valeur bien plus importante que work_mem.
Une grande valeur peut améliorer les performances des opérations VACUUM et de la
restauration des sauvegardes.
Valeur recommandée : 128MB (256MB à 1GB pour une mémoire totale de 24 Go
RAM)

Consommation des ressources : Write Ahead Log

Write-Ahead Logging (WAL) est une méthode conventionnelle pour s'assurer de

l'intégrité des données.
Le concept central du WAL est d'effectuer les changements des fichiers de données
(donc les tables et les index) uniquement après que ces changements ont été écrits
de façon sûre dans un journal, appelé journal des transactions.
Il n'est pas nécessaire d'écrire les pages de données vers le disque à chaque
validation de transaction car, dans l'éventualité d'une défaillance, on pourra
récupérer la base de données en utilisant le journal des transactions.

wal_buffers (integer)
Définit la quantité de mémoire partagée utilisée pour les données des journaux de
transactions qui n'ont pas encore été écrites sur disque.
Elle indique que les informations, pour annuler les effets d'une opération sur un
objet, doivent être écrites dans le journal en mémoire stable avant que l'objet

15
 Paramétrage du serveur PostgreSQL pour l'exploitation des
données spatiales

modifié ne migre sur le disque.

Cette règle permet d'assurer l'intégrité des données lors d'une reprise après
défaillance. En effet, il suffira de lire le journal pour retrouver l'état de la base lors de
son arrêt brutal.
La taille de ce tampon nécessite simplement d'être suffisamment grand pour stocker
les données WAL pour une seule transaction.
Alors que la valeur par défaut est généralement suffisante, les données spatiales
tendent à être plus volumineuses.
Il est donc recommandé d'augmenter la quantité de mémoire spécifiée dans ce
paramètre.
Valeur recommandée : 1MB

checkpoint_segments (integer)
Nombre maximum de journaux de transaction entre deux points de vérification
automatique des WAL (chaque segment fait normalement 16 Mo).
Cette valeur définit le nombre maximum de segments des journaux (typiquement
16MB) qui doit être rempli entre chaque point de reprise WAL.
Un point de reprise WAL est une partie d'une séquence de transactions pour lequel on
garantit que les fichiers de données ont été mis à jour avec toutes les requêtes
précédant ce point.
À ce moment-là toutes les pages sont repérées sur le disque et les points de reprise
sont écrits dans le fichier de journal.
Cela permet au processus de reprise après défaillance de trouver les derniers points
de reprise et de récupérer l'état des données avant la défaillance.
Étant donné que les points de reprise nécessitent un repérage de toutes le pages
ayant été modifiées sur le disque, cela va créer une charge d'entrées/sorties
significative.
Le même argument que précédemment s'applique ici pour les données spatiales.
Augmenter cette valeur limitera le nombre de points de reprise, mais impliquera un
redémarrage plus lent en cas de défaillance.
La valeur par défaut est de 3 segments.
Dans les phases d'import de référentiel volumineux ou les opérations de
remontée de dump conséquent, ce qui constitue la grosse part du travail a
l'initialisation de la base le paramètre peut être porté à beaucoup plus (exemple
128*16MB soir 2 GB). Cela signifie que postgres n'exécute pas l'écriture physique
tant qu'il n'a pas atteint 2GB de donnés à écrire. Ceci afin de ne pas faire travailler
les disques pour des petites opérations en usage courant mais bien par block de
tâche à exécuter. Par ailleurs, même si 2GB ne sont pas écrits, si le paramètre
checkpoint_timeout est par défaut à 5min, postgresql écrira soit tous les 2GB soit
toutes les 5 minutes, le premier de l'un des deux termes atteint.
Valeur recommandée : 10 (et jusqu'à 128 pour 24 Go de RAM totale, en particulier
en cas d’imports volumineux).

16
 Paramétrage du serveur PostgreSQL pour l'exploitation
des données spatiales

Constantes de coût du planificateur

random_page_cost (floating point)

Initialise l'estimation faite par le planificateur du coût de récupération non-
séquentielle d'une page disque.
Mesurée comme un multiple du coût de récupération d'une page séquentielle, sa
valeur par défaut est 4.0
Cette valeur sans unité représente le coût d'accès aléatoire à une page du disque.
Cette valeur est relative aux autres paramètres de coût notamment l'accès séquentiel
aux pages, et le coût des opérations processeur.
Bien qu'il n'y ait pas de valeur magique ici, la valeur par défaut peut généralement
être optimisée.
Réduire cette valeur par rapport au paramètre seq_page_cost incite le système à
privilégier les parcours d'index.
L'augmenter donne l'impression de parcours d'index plus coûteux.
Valeur recommandée : 2.0

effective_cache_size (integer)
Initialise l'estimation faite par le planificateur de la taille réelle du cache disque
disponible pour une requête.
Ce paramètre est lié à l'estimation du coût d'utilisation d'un index : une valeur
importante favorise les parcours d'index, une valeur faible les parcours séquentiels.
Ce paramètre n'a pas d'influence sur la taille de la mémoire partagée allouée par
PostgreSQL et ne réserve pas non plus le cache disque du noyau, il n'a qu'un rôle
estimatif. Le système ne suppose pas non plus que les données reste dans le cache
du disque entre des requêtes.
La valeur par défaut est de 128MB.
Valeur recommandée : 512MB (peut être porté à 2/3 de la RAM totale pour un
serveur dédié).

Journalisation
Il est recommandé d'activer le journal des opérations.
Attention cependant car le fichier de log doit être analysé et nettoyé régulièrement.

logging_collector = on

17
 Paramétrage du serveur PostgreSQL pour l'exploitation des
données spatiales

On se reportera à la documentation de PostgreSQL3 pour la gestion détaillée.

il est possible d'activer un mécanisme de rotation des fichiers de log avec
log_truncate_on_rotation.
De nombreux paramètres permettent de définir les événements à tracer. La mise au
point est à affiner avec l'administrateur système.

Conseil : Outil d'aide au paramètrage

le site PostgreSQL configuration tool4 permet une approche des paramètres à utiliser
en fonction de la configuration du serveur.

3 - [Link]
4 - [Link]

18
Bonnes pratiques
III -
III
de sécurité

Sécurité contre l'intrusion

Bien gérer les utilisateurs (users) et les groupes (rôles) permet de bien
sécuriser ses bases, tant contre l'intrusion que contre les erreurs de
manipulation.

Les accès peuvent se gérer à plusieurs niveaux (base de données, schéma, table,
tablespace, function...).
On peut autoriser à un utilisateur (à tous les niveaux) :
 La lecture (select) : droit de consulter les données
 L'écriture (insert) : droit de créer de nouvelles données
 La modification (update) : droit de modifier des données existantes
 La suppression (delete) : droit de supprimer des données
 La création de clés primaires / étrangères (references) : droit de créer des
clés primaires et étrangères.
 La création de triggers (trigger) : droit de créer des triggers. Un trigger est
une fonction qui se déclenche lors d'un événement prédéfini.
L'utilisation des rôles et des droits associés permet de gérer de manière très fine les
accès et les utilisateurs, permettant ainsi d'assurer l'intégrité des données.
Typiquement, les données de référence seront en lecture seule (sauf pour le ou les
administrateurs SIG) et les données métier seront lisibles uniquement pour les
services concernés.
La définition des groupes et rôles est réalisée au niveau du serveur par
l’administrateur.
La conception de ces rôles et droits d'accès est aussi importante que la conception de
la base de données elle-même.

19
 Bonnes pratiques de sécurité

ASGARD5 est une extension mettant à disposition des services qui proposent une
solution de gestion des droits intégrée qui, sans nécessiter de compétences avancées
sur PostgreSQL, qui, sans nécessiter de compétences avancées sur PostgreSQL, est
adaptée au contexte d'un serveur de données partagé, notamment du point de vue
de la sécurité .

Faire des sauvegardes (dumps) régulières voire mettre en place un système

de réplication

Il est indispensable de mettre en place un système de sauvegarde des bases de

données, organisé selon les besoins.
Le plus simple est de faire des dumps réguliers des bases de données.
Il est important de stocker ces dumps sur une autre machine que celle du serveur
PostgreSQL pour garantir leur disponibilité en cas de problème.
(par un point de montage6 ou une solution de sauvegarde automatique type arkeia
software).
En cas d'incident les données restaurées dateront du moment de la sauvegarde. Il
est conseillé d'effectuer ce type de sauvegarde à des moments de faible charge du
serveur.
Source documentaire : [Link] 7
Il est aussi possible de mettre en place un système de réplication de la base qu'il soit
synchrone (en quasi temps-réel) ou asynchrone.
On parle alors de serveurs maîtres et esclaves.
Cette fonctionnalité est disponible par défaut dans PostgreSQL mais il existe aussi
des extensions pour faciliter la réplication, la synchronisation ou la répartition des
charges, qui peuvent avoir certains avantages (pgCluster, pgpool, slony etc).

Complément : Gestion d'un sinistre

On pourra lire avec intérêt la formation de Dalibo 8 sur se sujet, et retenir surtout de
ne rien faire que l'on ne maîtrise pas !

5- [Link]
6- [Link]
7- [Link]
8- [Link]

20
 Optimisation de la
IV -
IV
gestion de la base
de données

1) Vérifier la validité de la géométrie

Une géométrie non valide est la cause principale de l'échec des requêtes spatiales.
La notion de validité de la géométrie et les méthodes pour corriger les géométries
non valides sont exposées dans le module 4 Compléments SQL - p.29
Un rapport9 plus général a été publié par le Pôle Progiciels géomatiques.

2) Répartir les tables volumineuses sur plusieurs tables

Le partitionnement10 consiste à diviser une grande table en plusieurs tables plus

petites.
Le partitionnement peut offrir plusieurs avantages :
 Les performances des requêtes peuvent être améliorées de façon
spectaculaire dans certaines situations (meilleure efficience des index).
 Le partitionnement devrait être appliqué dès que la taille de la table est
supérieure à la mémoire physique du serveur de base de données

9 - [Link]
10 - [Link]

21
 Optimisation de la gestion de la base de données

Pour plus de précisions, consulter la page : [Link]

partitioning.html11

3) Bien gérer les contraintes (clés primaires et étrangères)

Le module 2 Rappels et concepts de base détaille les notions de clé primaire et

étrangère - p.38.

4) Utiliser les index (spatiaux ou non) pour accélérer les

requêtes

L'utilisation d'index est une façon habituelle d'améliorer les performances d'une base
de données.
Un index permet au serveur de bases de données de retrouver une ligne spécifique
bien plus rapidement qu'en parcourant séquentiellement la table.
Les index peuvent aussi bénéficier aux commandes UPDATE et DELETE comportant
des conditions de recherche.
De plus, les index peuvent être utilisés dans les jointures. Ainsi, un index défini sur
une colonne qui fait partie d'une condition de jointure peut aussi accélérer
significativement les requêtes avec jointures.

11 - [Link]

22
 Optimisation de la gestion de la base de données

Mais les index ajoutent aussi une surcharge au système de base de données dans
son ensemble, si bien qu'ils doivent être utilisés avec discernement.
Après la création d'un index, le système doit le maintenir synchronisé avec la table.
Cela rend plus lourdes les opérations de manipulation de données.
C'est pourquoi les index qui sont peu, voire jamais, utilisés doivent être supprimés.
Attention : la présence d'index ralentit très fortement l'insertion donc il ne faut pas
en mettre lorsque cela n'est pas nécessaire.
Source documentaire : [Link] 12

5) Autres actions de maintenance de la base

Effectuer régulièrement des VACUUM et ANALYSE (auto-vacuum par exemple)

 Effectuer une réindexation régulière
 Utiliser la commande CLUSTER pour les tables géographiques
Ces commandes sont expliquées dans le module 2 Administration - p.40

12 - [Link]

23
Optimisation des
V -
V
requêtes SQL

Mettre les conditions (where) les plus "rapides" en premier

L'analyseur de requêtes se charge lui-même d'adopter la stratégie d'exécution de la

requête théoriquement la plus rapide.
Il détermine donc l'ordre d'exécution des conditions.
Dans certains cas où l'analyseur n'a pas assez d'éléments pour choisir, mettre en
premier la condition la plus "rapide" ou la plus discriminante permet d'optimiser les
requêtes.

Utiliser les sous-requêtes

Dans certains cas, l'utilisation de sous-requêtes est plus efficace que les multiples
conditions et jointures.
Il faut donc penser à tester l'utilisation de sous-requêtes, d'autant plus qu'elles
permettent de décomposer un problème complexe en plusieurs problèmes plus
simples

25
 Optimisation des requêtes SQL

Utilisation des fonctions spatiales préfixées ST_ dans les

requêtes spatiales

Les fonctions spatiales de PostGIS (préfixées ST_ pour respecter le standard

SQL/MM) comprennent pour la plupart l'appel intégré de l'opérateur spatial &&.
Cet opérateur utilise les index spatiaux pour effectuer un pré-tri résultant de
l'intersection des rectangles englobants des objets (Bounding Box) ce qui accélère
considérablement l'exécution des requêtes spatiales.
Le tableau ci-dessous précise pour chaque fonction spatiale l'utilisation ou non de
l'opérateur spatial && :

Fonction spatiale Opérateur &&

intégré

ST_Contains Oui

ST_ContainsProperl Oui
y

ST_Covers Oui

ST_CoveredBy Oui

ST_Crosses Oui

ST_Disjoint Non

ST_Equals Oui

ST_Intersects Oui

ST_Overlaps Oui

ST_Relate Non

ST_Touches Oui

ST_Within Oui

Utilisation de la commande EXPLAIN

PostgreSQL réalise un plan de requête pour chaque requête qu'il reçoit.

Choisir le bon plan correspondant à la structure de la requête et aux propriétés des
données est absolument critique pour de bonnes performances, donc le système

26
 Optimisation des requêtes SQL

inclut un planificateur complexe qui tente de choisir les bons plans.

La commande EXPLAIN permet d'obtenir des informations sur le déroulement de la
requête pour évaluer son efficacité.
Cette commande affiche le plan d'exécution que l'optimiseur de PostgreSQL engendre
pour l'instruction fournie.
L'utilisation de la commande EXPLAIN a été détaillée dans le module 5 Aller plus loin
- p.42.

27
 Contenus annexes

- Correction de géométrie

Notion de validité de la géométrie des entités

Une géométrie non valide est la cause principale de l'échec des requêtes spatiales.
Dans 90% des cas la réponse à la question “pourquoi mes requêtes me renvoient un
message d'erreur du type 'TopologyException error' est : “un ou plusieurs des
arguments passés sont invalides”. Ce qui nous conduit à nous demander : que
signifie invalide et pourquoi est-ce important ?
La validité est surtout importante pour les polygones, qui définissent des surfaces et
requièrent une bonne structuration.
Certaines des règles de validation des polygones semblent évidentes, et d'autres
semblent arbitraires (et le sont vraiment) :
 Les contours des polygones doivent être fermés.
 Les contours qui définissent des trous doivent être inclus dans la zone définie
par le contour extérieur.
 Les contours ne doivent pas s'intersecter (ils ne doivent ni se croiser ni se
toucher).
 Les contours ne doivent pas toucher les autres contours, sauf en un point
unique.
Les deux dernières règles font partie de la catégorie arbitraire.
PostGIS est conforme au standard OGC OpenGIS Specifications.
Les entités géométriques des bases PostGIS doivent ainsi être à la fois simples et
valides.
Par exemple, calculer la surface d'un polygone comportant un trou à l'extérieur ou
construire un polygone à partir d'une limite non simple n'a pas de sens.
Selon les spécifications de l'OGC, une géométrie simple est une géométrie qui ne
comporte pas de points géométriques anormaux, comme des auto-intersections ou
des auto-tangences, ce qui concerne essentiellement les points, les multi-points, les
polylignes et les multi-polylignes.
La notion de géométrie valide concerne principalement les polygones et les multi-
polygones et le standard définit les caractéristiques d'un polygone valide.

Un point est par nature simple, ayant une dimension égale à 0.

Un objet multi-points est simple si tous les points le composant sont distincts.

29
 Contenus annexes

Une polyligne est simple si elle ne se recroise pas (les extrémités peuvent être
confondues, auquel cas c'est un anneau et la polyligne est close).

Les polylignes (a) et (c) sont simples, mais pas les polylignes (b) et (d)

Une multi-polyligne est simple si toutes les polylignes la composant sont elles-mêmes
simples et si les intersections existant entre 2 polylignes se situent à une extrémité
de ces éléments :

(e) et (f) sont des multipolylignes simples, pas (g)

Un polygone est valide si ses limites, qui peuvent être constituées par un unique
anneau extérieur (polygone plein) ou par un anneau extérieur et un ou plusieurs
anneaux intérieurs (polygone à trous), ne comporte pas d'anneaux se croisant.
Un anneau peut intersecter la limite mais seulement en un point (pas le long d'un
segment).
Un polygone ne doit pas comporter de lignes interrompues (les limites doivent être

30
 Contenus annexes

continues) ou de point de rebroussement (pic).

Les anneaux intérieurs doivent être entièrement contenus dans la limite extérieure
du polygone.

(h) et (i) sont des polygones valides, (j), (k), (l), (m) sont des polygones ni simples
ni valides mais (j) et (m) sont des multi-polygones valides

Un multi-polygone est valide si et seulement si tous les polygones le composant sont

valides et si aucun intérieur d'un polygone ne croise celui d'un autre.
Les limites de 2 polygones peuvent se toucher, mais seulement par un nombre fini de
points (pas par une ligne).

(n) et (o) ne sont pas des multi-polygones valides, par contre (p) est valide

Par défaut, PostGIS n'applique pas le test de validité géométrique lors de l'import
d'entités géométriques, parce que le test de validité géométrique consomme
beaucoup de temps processeur pour les géométries complexes, notamment les
polygones.
Il faut donc mettre en œuvre différentes méthodes pour vérifier la validité de la
géométrie des entités.

31
 Contenus annexes

Exemple

Exemple :
Le polygone POLYGON((0 0, 0 1, 2 1, 2 2, 1 2, 1 0, 0 0)) n'est pas valide

Le contour externe est exactement en forme en 8 avec une intersection au milieu.

Notez que la fonction de rendu graphique est tout de même capable d'en afficher
l'intérieur, donc visuellement cela ressemble bien à une “aire” : deux unités carré,
donc une aire couplant ces deux unités.
Essayons maintenant de voir ce que pense la base de données de notre polygone en
calculant sa surface :
SELECT ST_Area(ST_GeometryFromText('POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1
2, 1 1, 1 0, 0 0))'));

Que ce passe-t-il ici ?

L'algorithme qui calcule la surface suppose que les contours ne s'intersectent pas.
Un contour normal devra toujours avoir une surface qui est bornée (l'intérieur) dans
un sens de la ligne du contour (peu importe le sens).
Cependant, dans notre figure en 8, le contour externe est à droite de la ligne pour un
lobe et à gauche pour l'autre.
Cela entraîne que les surfaces qui sont calculées pour chaque lobe annulent la
précédente (l'une vaut 1 et l'autre -1) donc le résultat est une surface égale à 0.

32
 Contenus annexes

- Détecter la validité
Dans l'exemple précédent nous avions un polygone que nous savions non-valide.
Comment déterminer les géométries non valides dans une table d'un million
d'enregistrements ?
Avec la fonction ST_IsValid(geometry) utilisée avec notre polygone précédent, nous
obtenons rapidement la réponse :
SELECT ST_IsValid(ST_GeometryFromText('POLYGON((0 0, 0 1, 1 1, 2 1, 2
2, 1 2, 1 1, 1 0, 0 0))'));
résultat : f (false)
Maintenant nous savons que la géométrie de l'entité n'est pas valide mais nous ne
savons pas pourquoi.
Nous pouvons utiliser la fonction ST_IsValidReason(geometry) pour trouver la cause
de non validité :
SELECT ST_IsValidReason(ST_GeometryFromText('POLYGON((0 0, 0 1, 1 1,
2 1, 2 2, 1 2, 1 1, 1 0, 0 0))'));
résultat : Self-intersection[1 1]
En plus de la cause d'invalifdité (auto-intersection), la localisation de la non validité
(coordonnée (1 1)) est aussi renvoyée.
Nous pouvons aussi utiliser la fonction ST_IsValid(geometry) pour tester les tables
comme dans l'exemple suivant :
SELECT ST_IsValidReason(geom) FROM [Link] WHERE Not
ST_IsValid (geom);

Détecter et corriger les erreurs géométriques par la pratique

Nous ébauchons ici la problématique des géométries invalides et des corrections

envisageables.
Pour créer une table de géométries invalides dans le schéma travail:
exécuter le script SQL ci-dessous :
CREATE TABLE [Link] (id serial, type varchar(20), geom
geometry(MULTIPOLYGON, 2154), PRIMARY KEY(id));
INSERT INTO [Link] (type, geom) VALUES ('Hole Outside
Shell', ST_multi(ST_GeomFromText('POLYGON((465000 6700000, 465010
6700000, 465010 6700010, 465000 6700010, 465000 6700000), (465015
6700015, 465015 6700020, 465020 6700020, 465020 6700015, 465015
6700015))',2154)));
INSERT INTO [Link] (type, geom) VALUES ('Nested Holes',
ST_multi(ST_GeomFromText('POLYGON((465030 6700000, 465040 6700000,
465040 6700010, 465030 6700010, 465030 6700000), (465032 6700002, 465032
6700008, 465038 6700008, 465038 6700002, 465032 6700002), (465033
6700003, 465033 6700007, 465037 6700007, 465037 6700003, 465033
6700003))',2154)));
INSERT INTO [Link] (type, geom) VALUES ('Dis.
Interior', ST_Multi(ST_GeomFromText('POLYGON((465060 6700000, 465070

33
 Contenus annexes

6700000,465070 6700010, 465060 6700010, 465060 6700000), (465065

6700000, 465070 6700005, 465065 6700010, 465060 6700005, 465065
6700000))', 2154)));
INSERT INTO [Link] (type, geom) VALUES ('Self
Intersect.', ST_multi(ST_GeomFromText('POLYGON((465090 6700000, 465100
6700010, 465090 6700010, 465100 6700000, 465090 6700000))',2154)));
INSERT INTO [Link] (type, geom) VALUES ('Ring Self
Intersect.', ST_multi(ST_GeomFromText('POLYGON((465125 6700000, 465130
6700000, 465130 6700010, 465120 6700010, 465120 6700000, 465125 6700000,
465123 6700003, 465125 6700006, 465127 6700003, 465125
6700000))',2154)));
INSERT INTO [Link] (type, geom) VALUES ('Nested
Shells', ST_multi(ST_GeomFromText('MULTIPOLYGON(((465150 6700000, 465160
6700000, 465160 6700010, 465150 6700010, 465150 6700000)),(( 465152
6700002, 465158 6700002, 465158 6700008, 465152 6700008, 465152
6700002)))',2154)));
Visualiser la table dans QGIS :

Chacun de ces objets est invalide.

Vérifions-le avec la requête suivante :
SELECT id, type, ST_IsValidReason(geom) FROM [Link]
WHERE NOT ST_IsValid(geom);
qui nous renvoi :

Méthode : Correction avec ST_MakeValid()

Executer le script SQL :
CREATE TABLE [Link] AS
(SELECT id, type, ST_MULTI(ST_MakeValid(geom))::geometry(MULTIPOLYGON,
2154) AS geom FROM [Link] WHERE NOT St_IsValid(geom));

34
 Contenus annexes

qui renvoi :

Charger la nouvelle table dans QGIS :

Seul le dernier élément est différent :

constater que la requête :
SELECT id, type, ST_IsValidReason(geom) FROM [Link]
WHERE NOT ST_IsValid(geom);
Ne trouve plus d'erreur.
Exécuter :
SELECT st_AsText(geom) from [Link] ;
qui renvoi :
"MULTIPOLYGON(((465000 6700000,465000 6700010,465010 6700010,465010
6700000,465000 6700000)),((465015 6700015,465015 6700020,465020
6700020,465020 6700015,465015 6700015)))"
"MULTIPOLYGON(((465030 6700000,465030 6700010,465040 6700010,465040
6700000,465030 6700000),(465032 6700002,465038 6700002,465038
6700008,465032 6700008,465032 6700002)),((465033 6700003,465033
6700007,465037 6700007,465037 6700003,465033 6700003)))"
"MULTIPOLYGON(((465065 6700000,465060 6700000,465060 6700005,465065
6700000)),((465060 6700005,465060 6700010,465065 6700010,465060
6700005)),((465065 6700010,465070 6700010,465070 6700005,465065
6700010)),((465070 6700005,465070 6700000,465065 6700000,4650 (...)"
"MULTIPOLYGON(((465090 6700000,465095 6700005,465100 6700000,465090
6700000)),((465095 6700005,465090 6700010,465100 6700010,465095
6700005)))"
"MULTIPOLYGON(((465125 6700000,465120 6700000,465120 6700010,465130
6700010,465130 6700000,465125 6700000),(465125 6700000,465127
6700003,465125 6700006,465123 6700003,465125 6700000)))"
"MULTIPOLYGON(((465150 6700000,465150 6700010,465160 6700010,465160
6700000,465150 6700000),(465152 6700002,465158 6700002,465158
6700008,465152 6700008,465152 6700002)))"
On peut comparer avec la table de départ.
SELECT st_AsText(geom) from [Link]
renvoi :
"MULTIPOLYGON(((465000 6700000,465010 6700000,465010 6700010,465000
6700010,465000 6700000),(465015 6700015,465015 6700020,465020

35
 Contenus annexes

6700020,465020 6700015,465015 6700015)))"

"MULTIPOLYGON(((465030 6700000,465040 6700000,465040 6700010,465030
6700010,465030 6700000),(465032 6700002,465032 6700008,465038
6700008,465038 6700002,465032 6700002),(465033 6700003,465033
6700007,465037 6700007,465037 6700003,465033 6700003)))"
"MULTIPOLYGON(((465090 6700000,465100 6700010,465090 6700010,465100
6700000,465090 6700000)))"
"MULTIPOLYGON(((465125 6700000,465130 6700000,465130 6700010,465120
6700010,465120 6700000,465125 6700000,465123 6700003,465125
6700006,465127 6700003,465125 6700000)))"
"MULTIPOLYGON(((465150 6700000,465160 6700000,465160 6700010,465150
6700010,465150 6700000)),((465152 6700002,465158 6700002,465158
6700008,465152 6700008,465152 6700002)))"
"MULTIPOLYGON(((465060 6700000,465070 6700000,465070 6700010,465060
6700010,465060 6700000),(465065 6700000,465070 6700005,465065
6700010,465060 6700005,465065 6700000)))"
'Hole Outside Shell' (trou extérieur à l'enveloppe) : un seul polygone composé d'un
trou en dehors du polygone de départ est devenu un multi-polygone composé de
deux polygones.
'Nested Holes' (trous imbriqués) : est devenu un multipolygone composé d'un
polygone avec trou et d'un deuxième polygone qui est au centre (le plus petit).
'Disconnected Interior' : (le trou touche le polygone en plus de 1 point) : est
devenu un multi-polygone composé de 4 polygones en triangle.
'Self Intersection' (auto-intersection) : un multi-polygone composé de deux
polygones en triangle.
'Ring Self Intersection'(anneau auto-intersectant, avec ici réduction de l'anneau en
un point) : est devenu un polygone à trou (trou en contact en 1 point avec
l'enveloppe ce qui est correct au sens de geos).
'Nested shell' (polygones imbriqués) : est devenu un polygone à trou.

Complément
A la recherche d'une requête universelle...
La requête suivante permet de prendre en compte des cas de figure où
ST_MakeVAlid() transforme des objets en collection de (multi)polygones,
(multi)polyliges, ou (multi)point.
C'est le cas si, par exemple on part d'une couche avec un polygone comprenant un
arc qui deviendra avec ST_Makevalid() une collection d'un polygone et d'une
polyligne.
On peut utiliser :
update ma_table SET geom =
ST_Multi(ST_CollectionExtract(st_simplify(ST_ForceCollection(ST_MakeValid(geom)),
0),3))
st_simplify(geometry,0) permet de supprimer les nœuds en double.
ST_ForceCollection() permet de forcer le résultat comme une collection, même si
St_MakeValid() ne produit pas de collection, afin de pouvoir utiliser après
St_CollectionExtract()
On utilise ST_CollectionExtract(geometry,3) pour ne retenir dans cet exemple que
les polygones de la collection.
et ST_Multi() pour forcer le résultat en multipolygones, même s'il n'y a qu'un seul
polygone extrait.

36
 Contenus annexes

Enfin pour ne pas simplifier inutilement des objets (par exemple linéaire dans notre
exemple ou l'on recherche des polygones) on utilisera finalement la requête
suivante :
update ma_table SET geom =
st_multi(ST_Simplify(ST_Multi(ST_CollectionExtract(ST_ForceCollection(ST_MakeVali
d(geom)),3)),0)) WHERE ST_GeometryType(geom) like ' %Polygon' and
St_invalid(geom)

Méthode : Correction par ST_Buffer(geom,0)

Une autre solution souvent proposée sur les forums est de corriger les erreurs de
géométrie en réalisant un buffer nul.
Executer le script suivant :
CREATE TABLE [Link] AS
(SELECT id, type, ST_Multi(ST_Buffer(geom,0))::geometry(MULTIPOLYGON,
2154) FROM [Link] WHERE NOT ST_IsValid(geom));
Charger cette couche dans QGIS :

Constater que les parties de polygones en jaune ont disparu en particulier pour les
polygones en 'papillons'.
La méthode avec st_buffer(geom, 0) est donc une alternative, si la méthode
st_makevalid() échoue (gros jeu de données), mais elle ne doit être utilisée que si on
a déjà corrigé les polygones en ‘papillons'
Il est fortement conseillé de noter le nombre d'objets de la couche avant et après
traitement pour une première vérification de pertinence.

Complément
Il existe l'algorithme Réparer les géométries dans Processing (menu traitement).
Nous n'avons abordé ici que la problématique des géométries invalides au sens de
l'osgeo. Mais il peut exister d'autres motif de correction comme des incohérences
entres polygones d'une même couche (dépend des spécifications de saisie), comme
des recouvrements (partiels ou non), des trous, des petits polygones (scories),...
Il existe d'autres solutions pour experts, comme l'utilisation de fonctions de GRASS
([Link] et [Link]) . Le plugin vérificateur de géométrie 13 qui est une extension
principale de QGIS est désormais relativement stable (QGIS 3 et plus) et intéressant.
Voir la documentation14 de QGIS.

Complément : Pour en savoir plus...

consulter la page spécifique15 sur le site Géoinformation.

13 - [Link]
14 - [Link]
15 - [Link]

37
 Contenus annexes

Philippe Desboeufs a également créé un plugin 'Le Nettoyeur (de polygones)16',

disponible dans le répertoire des plugins du MTE17.

- Clef primaire et clefs étrangères

Une clef primaire sert à identifier une ligne d'une table de façon unique. Dans un
SGBDR la clef primaire est identique à une contrainte d'unicité et une contrainte NOT
NULL, composée de une ou plusieurs colonnes.
Exemple en SQL :
CREATE TABLE exemple (a integer, b integer, c integer, PRIMARY
KEY(a,b)) ;
Une clé primaire indique qu'une colonne ou un groupe de colonnes (dans l'exemple
ci-dessus a et b) peut être utilisé(e) comme identifiant unique des lignes de la table.
L'ajout d'une clé primaire créera automatiquement un index unique sur la colonne ou
le groupe de colonnes utilisé dans la clé primaire. Une table a, au plus, une clé
primaire.
Le choix d'une clé primaire est une étape importante dans la conception d'une table.
Il peut être nécessaire d'ajouter une colonne qui soit auto-incrémentée.
Si on considère pour les besoins de l'exemple, que l'on souhaite pouvoir gérer un
propriétaire d'un véhicule même s'il ne possède pas de permis, alors le numéro de
permis ne peut être la clef primaire. On va donc définir une nouvelle colonne id qui
sera incrémentée automatiquement.
(On pourra consulter en complément cet extrait18 de la formation de Stéphane Crozat
sur les clefs artificielles et les clefs signifiantes)
Le SQL pour créer la table s'écrira :
CREATE TABLE proprietaire (
id SMALLINT AUTO_INCREMENT,
numeropermis VARCHAR(20),
nom VARCHAR(30) NOT NULL,
prenom VARCHAR(30),
PRIMARY KEY (id)
)

16 - [Link]
17 - [Link]
18 - [Link]

38
 Contenus annexes

Nous verrons qu'il est possible de réaliser les requêtes SQL à l'aide des assistants
sous PgAdminIII et vérifier la syntaxe SQL dans le dernier onglet de l'assistant :

on notera que smallserial et smallint AUTO_INCREMENT sont équivalent, de même

que VARCHAR et character varying.

Complément : Les séquences et type sérié

Sous PostgreSQL on peut utiliser les séquences19 pour générer une clef primaire. Voir
également les types sériés20.

Une contrainte de clé étrangère stipule que les valeurs d'une colonne (ou d'un groupe
de colonnes) doivent correspondre aux valeurs qui apparaissent dans les lignes d'une
autre table. On dit que cela maintient l'intégrité référentielle entre les deux tables.
Si nous reprenons notre exemple Propriétaire / véhicule.
Si on considère qu'un véhicule doit toujours avoir un propriétaire préalablement
existant, on peut déclarer :
CREATE TABLE vehicule (
numeroserie VARCHAR(20) PRIMARY KEY,
type VARCHAR(20),

19 - [Link]
20 - [Link]

39
 Contenus annexes

marque VARCHAR(20),
id_proprietaire SMALLINT REFERENCE proprietaire(id)
)
Lors de la saisie d'un nouveau véhicule, le système vérifiera que l'id saisie dans
id_proprietaire existe bien dans la table proprietaire.
Une table peut contenir plusieurs contraintes de clé étrangère. Les relations n-m
entre tables sont implantées ainsi (voir par exemple l'extrait de la formation de
Stéphane Crozat à partir d'ici21).
voir également la documentation de PostgreSQL22.

- Opérations de maintenance sur les bases

Sous PgAdmin il est possible d'utiliser le menu Outils > Maintenance...
celui-ci permet de lancer différentes opération de maintenance.

Méthode : VACUUM
VACUUM récupère l'espace inutilisé et, optionnellement, analyse une base.
VACUUM récupère l'espace de stockage occupé par des lignes mortes. Lors des
opérations normales de PostgreSQL, les lignes supprimées ou rendues obsolètes par
une mise à jour ne sont pas physiquement supprimées de leur table. Elles restent
présentes jusqu'à ce qu'un VACUUM soit lancé. C'est pourquoi, il est nécessaire de
faire un VACUUM régulièrement, spécialement sur les tables fréquemment mises à
jour.
Sans paramètre, VACUUM traite toutes les tables de la base de données courante
pour lequel l'utilisateur connecté dispose du droit d'exécution du VACUUM. Avec un
paramètre, VACUUM ne traite que cette table.
Les options :
 FULL récupère plus d'espace (compactage des tables), mais est beaucoup
plus long et prend un verrou exclusif sur la table (inaccessible à d'autres

21 - [Link]
22 - [Link]

40
 Contenus annexes

requêtes pendant le VACCUM). Cette méthode requiert aussi un espace disque

supplémentaire, car elle écrit une nouvelle copie de la table et ne supprime
l'ancienne copie qu'à la fin de l'opération. Habituellement, cela doit seulement
être utilisé quand une quantité importante d'espace doit être récupérée de la
table. .
 FREEZE : les versions des lignes sont gelées si elles sont suffisamment
vieilles pour être visibles de toutes les transactions en cours. En particulier,
sur une base en lecture seulement, VACUUM FREEZE aura pour résultat de
geler toutes les lignes de la base. Donc, tant que la base n'est pas modifiée,
aucun nettoyage supplémentaire n'est nécessaire.
 ANALYZE : Met à jour les statistiques utilisées par l'optimiseur pour
déterminer la méthode la plus efficace pour exécuter une requête.
Pour exécuter un VACUUM sur une table, vous devez habituellement être le
propriétaire de la table ou un superutilisateur. Néanmoins, les propriétaires de la
base de données sont autorisés à exécuter VACUUM sur toutes les tables de leurs
bases de données, sauf sur les catalogues partagés. Cette restriction signifie qu'un
vrai VACUUM sur une base complète ne peut se faire que par un superutilisateur.)
Il est recommandé que les bases de données actives de production soient traitées
par VACUUM fréquemment (au moins toutes les nuits), pour supprimer les lignes
mortes. Après avoir ajouté ou supprimé un grand nombre de lignes, il peut être utile
de faire un VACUUM ANALYZE sur la table affectée. Cela met les catalogues système
à jour de tous les changements récents et permet à l'optimiseur de requêtes de
PostgreSQL™ de faire de meilleurs choix lors de l'optimisation des requêtes
PostgreSQL inclut un « autovacuum23 » qui peut automatiser la maintenance par
VACUUM. Certains administrateurs de bases de données voudront suppléer ou
remplacer les activités du démon avec une gestion manuelle des commandes
VACUUM, qui seront typiquement exécutées suivant un planning par des scripts cron
ou par le planificateur de tâches.
A noter que si on est positionné dans le navigateur au niveau d'un objet (table,...) la
maintenance ne s’exécutera qu'au niveau de l'objet sélectionné.
À noter que le VACCCUM est également disponible pour l'administrateur système
sous forme de la commande système vacuumdb.

Méthode : ANALYZE
ANALYZE24 collecte des statistiques sur le contenu des tables de la base de données
et stocke les résultats dans le catalogue système pg_statistic25.
L'optimiseur de requêtes les utilise pour déterminer les plans d'exécution les plus
efficaces.
ANALYZE peut être exécuté comme une option de VACUUM.

Méthode : REINDEX
REINDEX reconstruit un index en utilisant les données stockées dans la table,
remplaçant l'ancienne copie de l'index. Il y a plusieurs raisons pour utiliser
REINDEX :
 Un index a été corrompu et ne contient plus de données valides
 L'index en question a « explosé », c'est-à-dire qu'il contient beaucoup de
pages d'index mortes ou presque mortes

23 - [Link]
24 - [Link]
25 - [Link]

41
 Contenus annexes

 Vous avez modifié un paramètre de stockage (par exemple, fillfactor) pour un

index et vous souhaitez vous assurer que la modification a été prise en
compte.
 La construction d'un index avec l'option CONCURRENTLY a échoué, laissant un
index « invalide »
Dans les vieilles versions de PostgreSQL, le gain avec REINDEX peut-être énorme.
Nota bene : disponible pour l'administrateur système avec la commande
reindexdb26.

Méthode : CLUSTER
CLUSTER commande permet de réécrire les données d'une table dans un ordre
donné (suivant un index). Cela peut être utile pour optimiser des requêtes SQL, selon
la façon dont les données sont accédées.
Pour des tables spatiales, elle permet d'ordonner physiquement la table selon l'index
géométrique et permet donc un accès aux enregistrements "proches" plus rapide.
Attention toutefois : cette opération reste lourde puisqu'elle pause un verrou exclusif
sur la table traitée (aucune autre action possible sur la table durant ce temps).

- L'optimisation des requêtes avec EXPLAIN

La performance des requêtes peut être affectée par un grand nombre d'éléments.
Certains peuvent être contrôlés par l'utilisateur, d'autres sont fondamentaux au
concept sous-jacent du système.
PostgreSQL réalise un plan de requête pour chaque requête qu'il reçoit. Choisir le bon
plan correspondant à la structure de la requête et aux propriétés des données est
absolument critique pour de bonnes performances, donc le système inclut un
planificateur complexe qui tente de choisir les bons plans.
Optimiser les requêtes peut-être un art difficile, mais il est bon de connaître les
rudiments.
Vous pouvez utiliser la commande 27 pour voir quel plan de requête le planificateur
crée pour une requête particulière.
On pourra également lire le chapitre sur l'utilisation des index28.
Le cours de Stéphane CROZAT29 constitue également une bonne introduction non
orienté sur la géomatique.

Exemple d'optimisation avec création d'index

Sous PgAdmin, exécuter l'ordre SQL suivant sur la table FR_communes du schéma
travail de la base stageXX :
select * from travail."FR_communes" where "Code_Commune" = '023' and
"Statut" = 'Commune simple' and "Nom_Région" = 'AQUITAINE'

26 - [Link]
27 - [Link]
28 - [Link]
29 - [Link]

42
 Contenus annexes

noter le temps qui apparaît en bas à droite ou dans l'onglet 'messages' :

En fait si on re-execute plusieurs fois la requête on s’aperçoit d'une assez grande

dispersion des résultats, mais l'ordre de grandeur reste le même.
Dans notre cas le temps d'exécution moyen est d'environ 140 ms.
Lancer maintenant la requête avec l'option EXPLAIN :

vous devez voir apparaître dans l'onglet 'EXPLAIN'

En positionnant le curseur sur l'icône des explications détaillées apparaissent :

Seq scan : indique que le moteur SQLde PostgreSQL execute un balayage séquentiel
sur la table FR_communes
startup cost : est le coût estimé de lancement
Total cost : est le coût estimé du scan (les coûts sont estimés en unité arbitraire30)
Plan rows : indique que 5 lignes sont retournées
Plan width : longueur estimée en octets
EXPLAIN fourni des données estimées.
On peut exécuter EXPLAIN ANALYZE qui lui va exécuter la requête et fournir les coûts

30 - [Link]

43
 Contenus annexes

mesurés

Ajoutons maintenant un index (de type btree) sur le champs"Statut"

puis, réexécuter la requête SQL

Normalement vous devez constater un temps d’exécution plus court (le cas échéant
exécuter plusieurs fois la requête pour avoir un temps moyen).
Relancer le EXPLAIN

Le moteur de PostgreSQL commence par balayer la table d'index sur le statut pour
un coût de 4.94, puis balaye uniquement les enregistrements de la table
FR_communes correspondant à la condition sur statut pour un coût de 299.15 (coût
total) - 4.94 (coût du balayage de la table d'index).
Le coût total est de 299.15 et donc très inférieur au coût sans l'index.

Il se peut cependant que le planificateur n'utilise pas ce nouvel index dans votre cas
particulier.
en effet les index ne sont pas systématiquement utilisés. Voir la documentation
PostgreSQL31.

31 - [Link]

44
 Contenus annexes

Ajouter maintenant un index supplémentaire sur le champ "Code_Commune" et un

autre sur le champ "Nom_Région"
relancer la requête et le EXPLAIN :

Si on survole l'icône finale à droite, on constate que le coût global est maintenant de
82.21
Le planificateur a choisi une stratégie :
1. balayage de l'index sur le champ Nom_Région en 57.66
2. balayage de l'index id_commune en 4.93
3. ET (Bitmap And) entre les deux résultats ci-dessus en 0
4. balayage des lignes de la table FR_Communes correspondantes en ajoutant le
filtre sur le Statut en (82.21- 62.84) = 19.77
Pour un total de 82.21.
Le planificateur aurait pu choisir une autre stratégie en inversant le rôle d'un ou de
deux des index. Il base sa stratégie sur les données statistiques qu'il maintient sur
les tables.
Une commande VACCUM ANALYZE que l'on peut lancer sur la base stageXX à partir
du menu Outils -> Maintenance
permet de remettre à jour les statistiques sur les tables.

Tentons maintenant une autre stratégie d'optimisation en utilisant plutôt un index

composite.
Supprimer les index créés sur la table (clic droit sur l'index -> supprimer)

45
 Contenus annexes

créer un index qui porte sur les champs statut et Nom_Région et Code_commune :

Relancer l'analyse sur la requête :

La stratégie est maintenant de balayer l'index composite, puis de rechercher les

enregistrements correspondant dans la table FR_communes pour un coût total de
18.13
A comparer avec les résultats précédents.
Bien entendu il ne faut pas abuser de la création des index (occupation disque et
ralentissement des requêtes en modifications), mais les utiliser a bon escient après
avoir diagnostiqué les requêtes coûteuses les plus fréquentes.

46
 Contenus annexes

Complément
EXPLAIN fourni des temps estimés, EXPLAIN ANALYZE fourni les temps observés
(exécute la requête), ce qui est beaucoup plus précis mais coûteux en temps.
Il est également possible d'avoir plus de détails avec EXPLAIN ANALYZE VERBOSE

ou directement en SQL :
explain analyze verbose select * from travail."FR_communes" where
"Code_Commune" = '023' and "Statut" = 'Commune simple' and "Nom_Région"
= 'AQUITAINE'
qui renvoi :
"Index Scan using id_composite on travail."FR_communes" (cost=0.41..18.13
rows=5 width=442) (actual time=0.024..0.032 rows=5 loops=1)"
" Output: id, geom, "Code_Commune", "INSEE_Commune", "Nom_Commune",
"Statut", "Population", "Superficie", "Altitude_Moyenne", "Code_Canton",
"Code_dpt_arr_can", "Code_Arrondissement", "Code_dpt_arr", "Code_Département",
"Nom_Département", "Code_Région", "Nom_Région""
" Index Cond: ((("FR_communes"."Statut")::text = 'Commune simple'::text) AND
(("FR_communes"."Nom_Région")::text = 'AQUITAINE'::text) AND
(("FR_communes"."Code_Commune")::text = '023'::text))"
"Planning time: 0.104 ms"
"Execution time: 0.056 ms"

Complément
PostgreSQL permet de mettre dans le fichier de log, les requêtes ayant plus pris plus
d'un certain temps. Cette option se configure via l'entrée
log_min_duration_statement du fichier de configuration PostgreSQL.
A noter également un site 32qui permet de mettre en exergue les parties les plus
coûteuses d'un plan d'analyse.
Dalibo propose PEV233 qui est utilisable en ligne34 ou en téléchargement.
On trouvera également des explications très détaillées sur ce site35 avec par exemple
la description des opérations36 des plans d'analyse.

Complément : Indexation des géométries dans des requêtes

complexes.
Si on utilise des sous-requêtes qui calculent des géométries (st_intersection,
st_buffer,...) pour ensuite les croiser avec des grosses tables (ex : département
entier), il faut savoir que les géométries calculées ne sont pas indexées. Une solution
d'optimisation peut alors être de créer une table résultat temporaire qui pourra être
elle, être indexée :
CREATE TEMP TABLE Matable AS... , puis CREATE INDEX monIndex ON MaTable

32 - [Link]
33 - [Link]
34 - [Link]
35 - [Link]
36 - [Link]

47
 Contenus annexes

USING GIST (the_geom),

au lieu de WITH Matable AS (SELECT...) qui ne permet pas de réaliser la phase
d'indexation.
Exemple :
Si on créé un index sur « Matable »
---creation d'un index sur la géométrie de "matable"
CREATE INDEX matable_the_geom_idx ON matable USING gist (the_geom);
Puis qu'on utilise, par exemple, un syntaxe CTE :
WITH tampons AS (SELECT st_buffer(the_geom) as geom from ma_table)
SELECT * from tampons where ST_area(geom) <100 ;
L'index n'est pas mobilisé puisque le WHERE n'utilise pas la géométrie indexée
the_geom, mais la géométrie calculée geom=st_buffer(the_geom)
Il pourrait donc être opportun de créer une table temporaire « tampons » plutôt que
d'utiliser le WITH.

48