Algorithmes et bases de données, année 2019
TD2: Introduction à PostgreSQL
L’objectif de ce TD:
- Se familiariser avec l’environnement PostgreSQL
- Manipuler et comprendre quelques variables importantes dans le fichier de configuration
- 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.
- 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
- 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
- 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
- pg_type
En utilisant la table pg_type affichez le nom et la taille des types utilisables dans une table.
- 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
.
Affichez la table des corrélations chacun des attributs.
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.
- Affichez les corrélations. Pourquoi la clef primaire à t-elle une aussi bonne corrélation?
Analyse de requêtes simples
Que retourne une simple requête cherchant toutes les villes de France?
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ûtCré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.
Créez des indexes dans cette nouvelle table pour les villes, les pays et pour les urls/dois.
Refaite les requêtes du TD1 en analysant ce que fait le planificateur en fonction de la clusterisation physique de la table.
Compiled the: mer. 08 janv. 2025 11:50:56 CET