Solution tricheur

S3
Année 2018-2019


Bases de données avancées

Optimisation des requêtes

Dans ce TP, nous travaillons sur la base de données des villes, régions et départements de France. Cette base de données comporte un grand nombre de données : 36684 villes, 26 régions et 100 départements ! Cette base de données a été téléchargée sur le site http://pgfoundry.org/projects/dbsamples/

L’objectif de ce TP est d’analyser les plans d’exécution des requêtes générés par l’optimiseur de Postgres et de mettre en place des mécanismes permettant d’améliorer le coût d’exécution des requêtes dans des bases de données de grades dimensions.
Le schéma de cette base de données est le suivant :
TOWNS (id, code, article, name, department)
REGIONS(id, code, capital, name)
DEPARTMENTS(id, code, capital, region, name)

Préambule : Création de la base de données et insertion des données
- Lancez un terminal (Terminal ou Konsole).
- Dans votre répertoire M3106C, créez un répertoire FrenchTowns.
- Dans ce sous-répertoire FrenchTowns, copier le fichier create.sql disponible dans /users/info/pub/S3/M3106C/FrenchTowns. Examinez ce fichier un peu différent des create.sql habituels…
    • Dans le terminal, connectez-vous sur Postgres  par la commande
psql –h postgres-info –U  users3a01  bases3a01   (par exemple)
    • Supprimez toutes les tables qui ont été créées dans les TP1 et 2 afin d’obtenir une base vierge.
    • Exécutez la commande \i create.sql pour créer les tables de la base de données et y insérer les données.
    • Vérifiez que les tables TOWNS, REGIONS et DEPARTMENTS ont été créées  par la commande \d. Vous remarquerez que des tables de type « SEQUENCE » ont également été créées. Une séquence est une table contenant une seule ligne appelée générateur de séquence, utilisée pour générer des identifiants uniques de lignes de tables (ici les identifiants des villes, régions et départements). Ces tables séquence sont des tables utilitaires. Vous remarquerez également que des index (cf cours) ont été créés automatiquement par Postgres pour tous les attributs UNIQUE, ceci afin d’optimiser le temps d’exécution des requêtes.
    • Pour répondre à certaines questions de ce TP, vous devrez effectuer certaines recherches sur la doc en ligne de Postgres : http://docs.postgresqlfr.org/

Exercice 1 : Etude des statistiques du schéma
Tapez la commande suivante :
SELECT relname, relpages, reltuples from pg_class order by relname ;

Question 1 : Que fait cette commande ?
Consultez la doc en ligne de postgres pour trouver ce que représente :
    • pg_class ?
    • relpages ?
    • reltuples ?
Notez les résultats obtenus pour les relnames suivants : departments, departments_capital_key, departments_code_key, departments_id_key, departments_id_seq, departments_name_key, towns, towns_id_key, towns_id_seq, towns_code_department_key, regions, regions_code_key, regions_id_key, regions_id_seq, regions_name_key.

Question 2 :
Afin que l’estimation du temps d’exécution d’une requête par Postgres soit le plus exact possible, il est nécessaire de mettre à jour les statistiques des tables.
Pour cela, exécutez la commande ANALYZE (que fait cette commande ?)

Ré-exécutez la commande :
SELECT relname, relpages, reltuples from pg_class order by relname ;
Et examinez les différences concernant les chiffres obtenus en question 1.

Exercice 2 : Etude des plans d’exécution de différents types de requêtes
Dans cet exercice, nous utiliserons la commande EXPLAIN qui donne le plan d’exécution et calcule les coûts d’exécution prévus par l’optimiseur de Postgres dans le cadre d’une requête.

Question 1 : Tapez la commande suivante :
EXPLAIN SELECT * FROM TOWNS ;
Qu’indiquent les différents coûts générés par cette commande ?
Quel est le plan d’exécution généré dans ce cas ?
RECHERCHE SEQUENTIELLE (seq scan)

Question 2 : RAJOUTER UN EXPLAIN DEVANT CHAQUE REQUETE :
Examinez les plans d’exécution des requêtes suivantes. Pour chaque plan d’exécution généré, dessinez sur papier l’arbre relationnel généré par l’optimiseur de Postgres et notez les résultats :
    1. Code et noms des villes de France
SELECT code, name
FROM TOWNS ;
TOUJOURS RECHERCHE SEQUENTIELLE (seq scan) mais width moins important car on ne récupère que le code et le name, pas tous les attributs.

    2. Noms, départements et régions de toutes les villes de France
SELECT T.name, department, region
FROM TOWNS T, DEPARTMENTS D
WHERE T.department = D.code ;
Effectuer une recherche sur internet pour comprendre ce que représentent « hash join » et « hash cond »

    3. Noms et départements des villes de la région Rhône-Alpes (avec une jointure)
SELECT D.name, department
FROM TOWNS T, DEPARTMENTS D
WHERE T.department = D.code and region = ‘Rhône-Alpes’ ;

    4. Noms et départements des villes de la région Rhône-Alpes (avec une requête imbriquée). Comparer cette version avec requête imbriquée avec la version précédente (question 3) sans requête imbriquée.

SELECT name, department
FROM TOWNS
WHERE department IN (SELECT code FROM DEPARTMENTS WHERE region = ‘Rhône-Alpes’) ;

    5. Nombre de villes de France. Comparer avec la question 1.
SELECT count(*)
FROM TOWNS ;

    6. Nombre de villes par département. Comparer avec la question 5. Effectuer une recherche sur internet pour comprendre ce que représente « hashAggregate »
SELECT department, count(*)
FROM TOWNS
GROUP BY department ;


    7. Nombre de départements par région
SELECT region, count(*)
FROM DEPARTMENTS
GROUP BY region ;

    8. Nombre de départements par région, pour les régions comportant plus de 5 départements. Comparer avec la question 7.
SELECT region, count(*)
FROM DEPARTMENTS
GROUP BY region
HAVING count(*) > 5 ;

Exercice 3 : Index et plan d’exécution
On rappelle la commande de création d’un index :
CREATE [UNIQUE] INDEX nom_index ON nom_table(liste_attributs) ;

Question 1 :
Examinez le plan d’exécution de la requête
SELECT * FROM TOWNS where name = ‘Grenoble’ ;   (noter les résultats)
Un index a-t-il été créé par Posgres sur l’attribut name de la relation TOWNS ? Pourquoi ?
NON car name n’est pas unique dans TOWNS.

Créez un index sur l’attribut name de TOWNS, puis ré-examinez le plan d’exécution de la requête ci-dessus.
Que notez-vous ?
Plus rapide et recherche indexée (index scan) plutot que recherche séquentielle.

Question 2 :
Cette question est basée sur la requête suivante :
    SELECT towns.code, towns.name
    FROM   towns, departments
    WHERE  towns.department = departments.code
      AND  departments.name = ’Isère’ ;

Exécutez cette requête et vérifiez les résultats.

Comparez les plans d’exécution générés par l’optimiseur de Postgres dans les cas suivants :
    • Cas par défaut, avec les index générés par postgres
    • Sans index (vous devrez préalablement supprimer tous les index générés par Postgres par la commande DROP INDEX nomIndex ou par la commande ALTER TABLE nomtable DROP CONSTRAINT nomcontrainte).
    • Index sur departments.name (vous devrez donc re-créer un nouvel index)
    • Index sur departments.code (vous devrez donc re-créer un nouvel index)
    • Index sur departments.code et towns.department (vous devrez donc re-créer deux nouveaux index)
    • Index sur departments.code, towns.department et departments.name vous devrez donc re-créer trois nouveaux index)
    • …

Quelles différences voyez-vous entre les plans d’exécution ainsi générés ?