Algorithmes et bases de données, année 2019

TD2: Introduction à PostgreSQL

L’objectif de ce TD:

  1. Se familiariser avec l’environnement PostgreSQL
  2. Manipuler et comprendre quelques variables importantes dans le fichier de configuration
  3. Analyser décortiquer des requêtes simples avec et sans indexes.

Si vous avez terminé le TD précédant, vous devez avoir charger à l’aide \copy les logs de scihub téléchargeable ici ici.

Pendant que vous faites les premières questions, relancez la compilation de PostgreSQL sur une machine virtuelle plus puissante et avec le maximum espace disque possible puis lancer le téléchargement d’une version plus conséquente de ces données que vous chargerais dans la même table (après avoir supprimer les données existante) ici.

Si vous utilisez psql, alors la commande \d vous sera utile dans la suite.

Pour ne pas avoir à rechercher tous temps l’adresse IP de votre machine virtuelle sur OpenStack, vous pouvez configurer votre fichier config du répertoire .ssh.

Les tables systèmes

Toute base de donnée dans un cluster possède par défault trois schémas disposant de tables et de vues pouvant être interoger avec une requête standard.

  1. information_schema:

Contient des informations sur les objets présent dans la bases de données. Par exemple les extension installées, langages pour le traitement des langues, liste des utilisateurs. Plus de détails ici

  1. pg_catalog:

Contient beaucoup ’informations sur la configuration la base, des statistiques d’utilisations, des accès à des informations systèmes (ect..). Plus de détails ici

  1. public : contient les tables de la base de données.

À l’aide d’une requête simple sur la table pg_tables, afficher le nom de toutes les tables de tous les schéma.

D’autres informations sont stockées dans des vues. Vous pouvez les afficher avec pg_views

Information_schema

En regardant dans la documentation en ligne, utilisez la vue information_schema.column pour trouver le nom, le type de chaque colomne de la table crée dans le TD1.

pg_catalog

  1. pg_type

En utilisant la table pg_type affichez le nom et la taille des types utilisables dans une table.

  1. pg_operator

En utilisant la table pg_operator, affichez le nom, le membre de droit, de gauche et de retour. Le membre de droit et le membre de gauche sont des OID (object Identifier). Il s’agit d’une column cachée (et désactivée par défault dans les tables définies par l’utilisateur)

Leur définition peut être trouvé dans pg_type. À l’aide d’une jointure, affichez le nom du type des membres de gauche, de droite

La table standard pg_stats

À l’aide de la commande \d, regarder la description de la table pg_stats.

  1. Affichez la table des corrélations chacun des attributs.

  2. Expliquez pourquoi de telle différence entre les corrélations.

Extraire de la table principale une sous table contenant uniquement la liste des villes et des pays sans répétitions avec une clés primaire. Utilisez l’aspirateur (VACUUM ANALYZE) pour mettre à jour les statistiques.

  1. Affichez les corrélations. Pourquoi la clef primaire à t-elle une aussi bonne corrélation?

Analyse de requêtes simples

  1. Que retourne une simple requête cherchant toutes les villes de France?

  2. Créez un index sur les villes et refaites cette même et utilisez la commande CLUSTER pour regrouper les tuples physiquement. Vérifiez que la corrélation à bien été modifié. Regardez le coût

  3. Créez un index sur les pays et refaite une analyze de la requête.

### Ces requêtes ne comportent pas assez de données pour que le nombre de pages deviennent problématiques. Tout tient dans le cache.

Bascule sur la nouvelle machine virtuelle

Pour contourner ce problème, dans la nouvelle machine virtuelle, on va réduire le cache drastiquement dans le fichier de configuration. On va mettre la mémoire partagée au minimum et on va charger le gros fichier dans une nouvelle base de donnée.

  1. Créez des indexes dans cette nouvelle table pour les villes, les pays et pour les urls/dois.

  2. Refaite les requêtes du TD1 en analysant ce que fait le planificateur en fonction de la clusterisation physique de la table.