Explications
Fichier CSV des départements français (95 lignes)
Extrait des premières lignes du fichier :
numero,nom,pop
1,Ain,529
2,Aisne,552
3,Allier,357
4,Alpes-de-Haute-Provence,145
5,Hautes-Alpes,127
6,Alpes-Maritimes,1023
7,Ardèche,295
8,Ardennes,299
9,Ariège,143
Fichier CSV des villes françaises (36700 lignes)
Extrait des premières lignes du fichier :
codeinsee,departement,nom,pop2010,pop1999
1284,1,Ozan,618,469
1123,1,Cormoranche-sur-Saône,1058,903
1298,1,Plagne,129,83
1422,1,Tossiat,1406,1111
1309,1,Pouillat,88,58
1421,1,Torcieu,698,643
1320,1,Replonges,3500,2841
1119,1,Corcelles,243,222
1288,1,Péron,2143,1578
Question
Instancier la base de données Postgres permettant de gérer ces fichiers.
Solution
CREATE TABLE dpt (
numero CHAR(2) PRIMARY KEY,
nom VARCHAR,pop INTEGER);
CREATE TABLE ville (
codeinsee CHAR(5) PRIMARY KEY,
departement CHAR(3),
nom VARCHAR,pop2010 INTEGER,pop1999 INTEGER);
\copy dpt FROM dpt_france.csv WITH CSV HEADER DELIMITER ','
\copy ville FROM villes_france.csv WITH CSV HEADER DELIMITER ','
Remarque :
Le fichier des départements n'est pas complet, la contrainte de clé étrangère ne peut pas être déclarée ici.
VACUUM
Exécutez la commande ci-après, on observe seulement 21300 lignes collectées, là ou 36700 étaient attendues.
EXPLAIN
SELECT * FROM ville;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on ville (cost=0.00..497.00 rows=21300 width=32)
Question
Expliquez pourquoi et proposez une solution.
Solution
Il faut exécuter la commande VACUUM ANALYZE pour commander au moteur de collecter des statistiques sur les données fraîchement entrées. Le résultat de la requête est alors celui attendu.
VACUUM ANALYSE;
EXPLAIN
SELECT * FROM ville;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on ville (cost=0.00..651.00 rows=36700 width=30)
Projection
Exécutez la commande ci-après permettant de projeter le nom des villes.
EXPLAIN
SELECT nom FROM ville;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on ville (cost=0.00..651.00 rows=36700 width=12)
Question
Quelle différence observez-vous avec le plan de la requête SELECT * FROM ville ? Expliquez.
Solution
La seule différence concerne la valeur de width qui passe de 30 à 12. Cette valeur indique la largeur moyenne (en octet) d'une ligne. La projection a logiquement réduit cette valeur.
Restriction et tri
Exécutez les commande ci-après permettant respectivement de faire une restriction et un tri sur le nom des villes.
EXPLAIN
SELECT nom FROM ville
WHERE nom='Compiègne';
QUERY PLAN
-------------------------------------------------------
Seq Scan on ville (cost=0.00..742.75 rows=1 width=12)
Filter: ((nom)::text = 'Compiègne'::text)
EXPLAIN
SELECT nom FROM ville
ORDER BY nom;
QUERY PLAN
-----------------------------------------------------------------
Sort (cost=3433.50..3525.25 rows=36700 width=12)
Sort Key: nom
-> Seq Scan on ville (cost=0.00..651.00 rows=36700 width=12)
Question
Qu'observez-vous ? Proposez une solution pour améliorer ces deux requêtes. Mesurez le gain apporté par la solution. Notez les éventuels inconvénient apportés par la solution.
Solution
Les coûts sont augmenté car il faut faire des recherches dans le champ nom. On peut optimiser ces requêtes en indexant ce champ.
CREATE INDEX idxnom ON ville(nom);
EXPLAIN
SELECT nom FROM ville
WHERE nom='Compiègne';
QUERY PLAN
-------------------------------------------------------------------------
Index Only Scan using idxnom on ville (cost=0.29..4.31 rows=1 width=12)
Index Cond: (nom = 'Compiègne'::text)
Le coût dans le pire cas a été réduit de 742 à 4,3 (gain d'un facteur 172).
On notera que le coût minimum, dans le cas où Compiègne est la première ville de la table est augmenté de 0 à 0,29 (c'est le coût fixe de l'accès à l'index). C'est ici une perte négligeable par rapport au gain dans le pire cas et qui ne se produira que dans des cas très rares.
On ne mesure pas ici les autres inconvénients de l'indexation (temps de mise à jour, espace disque complémentaire).
EXPLAIN
SELECT nom FROM ville
ORDER BY nom;
QUERY PLAN
--------------------------------------------------------------------------------
Index Only Scan using idxnom on ville (cost=0.29..1130.79 rows=36700 width=12)
Le coût pour récupérer la première ligne passe de 3433 à 0,29 (qui est le coût de l'accès à l'index). Il y a donc un gain très important, qui est intéressant si l'on souhaite transmettre les noms des villes progressivement et dans l'ordre du tri.
Le coût pour récupérer toutes les lignes passent de 3525 à 1130 (gain d'un facteur 3,1).
Jointure
Exécutez la commande ci-après permettant de faire une jointure.
EXPLAIN
SELECT v.nom, d.nom
FROM ville v JOIN dpt d
ON v.departement = d.numero;
Hash Join (cost=3.14..1136.96 rows=34520 width=22)
Hash Cond: (v.departement = d.numero)
-> Seq Scan on ville v (cost=0.00..651.00 rows=36700 width=16)
-> Hash (cost=1.95..1.95 rows=95 width=13)
-> Seq Scan on dpt d (cost=0.00..1.95 rows=95 width=13)
Question
Indexez la clé étrangère v.departement. Observez-vous pas de gain ? Expliquez.
Solution
CREATE INDEX idxdpt ON ville(departement);
Le plan d'exécution n'a pas changé du tout. Le hash join utilisé pour effectuer la jointure n'utilise pas l'index.
Question
À partir des plans précédents, calculez le gain qu'apporterait une dénormalisation à cette jointure.
Solution
Le plan serait similaire à celui de SELECT nom FROM ville (avec simplement un width plus important).
Le coût serait donc de 651 au lieu de 1136 : la requête serait 1,75 fois plus rapide.