Explications

Fichier CSV des départements français (95 lignes)

Fichier des départements français [zip]

Extrait des premières lignes du fichier  :

Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
numero,nom,pop
2
1,Ain,529
3
2,Aisne,552
4
3,Allier,357
5
4,Alpes-de-Haute-Provence,145
6
5,Hautes-Alpes,127
7
6,Alpes-Maritimes,1023
8
7,Ardèche,295
9
8,Ardennes,299
10
9,Ariège,143
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)

Fichier des communes françaises [zip]

Extrait des premières lignes du fichier :

Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
codeinsee,departement,nom,pop2010,pop1999
2
1284,1,Ozan,618,469
3
1123,1,Cormoranche-sur-Saône,1058,903
4
1298,1,Plagne,129,83
5
1422,1,Tossiat,1406,1111
6
1309,1,Pouillat,88,58
7
1421,1,Torcieu,698,643
8
1320,1,Replonges,3500,2841
9
1119,1,Corcelles,243,222
10
1288,1,Péron,2143,1578
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.

VACUUM

Exécutez la commande ci-après, on observe seulement 21300 lignes collectées, là ou 36700 étaient attendues.

num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
1
EXPLAIN
2
SELECT * FROM ville;
EXPLAIN
SELECT * FROM ville;
Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
                        QUERY PLAN
2
-----------------------------------------------------------
3
 Seq Scan on ville  (cost=0.00..497.00 rows=21300 width=32)
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on ville  (cost=0.00..497.00 rows=21300 width=32)

Question

Expliquez pourquoi et proposez une solution.

Projection

Exécutez la commande ci-après permettant de projeter le nom des villes.

num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
1
EXPLAIN
2
SELECT nom FROM ville;
EXPLAIN
SELECT nom FROM ville;
Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
                        QUERY PLAN
2
-----------------------------------------------------------
3
 Seq Scan on ville  (cost=0.00..651.00 rows=36700 width=12)
                        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.

Restriction et tri

Exécutez les commande ci-après permettant respectivement de faire une restriction et un tri sur le nom des villes.

num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
1
EXPLAIN
2
SELECT nom FROM ville
3
WHERE nom='Compiègne';
EXPLAIN
SELECT nom FROM ville
WHERE nom='Compiègne';
Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
                      QUERY PLAN
2
-------------------------------------------------------
3
 Seq Scan on ville  (cost=0.00..742.75 rows=1 width=12)
4
   Filter: ((nom)::text = 'Compiègne'::text)
                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on ville  (cost=0.00..742.75 rows=1 width=12)
   Filter: ((nom)::text = 'Compiègne'::text)
num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
1
EXPLAIN
2
SELECT nom FROM ville
3
ORDER BY nom;
EXPLAIN
SELECT nom FROM ville
ORDER BY nom;
Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
                           QUERY PLAN
2
-----------------------------------------------------------------
3
 Sort  (cost=3433.50..3525.25 rows=36700 width=12)
4
   Sort Key: nom
5
   ->  Seq Scan on ville  (cost=0.00..651.00 rows=36700 width=12)
                           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.

Jointure

Exécutez la commande ci-après permettant de faire une jointure.

num. | Ligne | texte | copie |
CTRL+C pour copier, CTRL+V pour coller
1
EXPLAIN
2
SELECT v.nom, d.nom
3
FROM ville v JOIN dpt d
4
ON v.departement = d.numero;
EXPLAIN
SELECT v.nom, d.nom
FROM ville v JOIN dpt d
ON v.departement = d.numero;
Ligne | num. | copie |
CTRL+C pour copier, CTRL+V pour coller
1
Hash Join  (cost=3.14..1136.96 rows=34520 width=22)
2
   Hash Cond: (v.departement = d.numero)
3
   ->  Seq Scan on ville v  (cost=0.00..651.00 rows=36700 width=16)
4
   ->  Hash  (cost=1.95..1.95 rows=95 width=13)
5
         ->  Seq Scan on dpt d  (cost=0.00..1.95 rows=95 width=13)
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.

Question

À partir des plans précédents, calculez le gain qu'apporterait une dénormalisation à cette jointure.