TD 5: Optimisation PostgreSQL

TD 5: Optimisation des performances d'une base de données PostgreSQL

• Objectif: Créer une base de données de gestion des commandes pour une entreprise de commerce électronique contenant 1 000 000 lignes de données.

Étapes et Concepts :

• 1. Connexion à PostgreSQL:

  • Utiliser la commande \c postgres; pour se connecter à la base de données PostgreSQL avec le compte postgres.

• 2. Création de la base de données:

  • Créer une nouvelle base de données nommée ecommerce avec la commande CREATE DATABASE ecommerce;

• 3. Connexion à la nouvelle base de données:

  • Se connecter à la base de données ecommerce avec la commande \c ecommerce;

• 4. Création des tables:

  • Créer la table clients avec les colonnes suivantes :
    • id SERIAL PRIMARY KEY (clé primaire auto-incrémentée)
    • nom VARCHAR(100)
    • email VARCHAR(100)
  • Créer la table produits avec les colonnes suivantes :
    • id SERIAL PRIMARY KEY (clé primaire auto-incrémentée)
    • nom VARCHAR(100)
    • prix DECIMAL
  • Créer la table commandes avec les colonnes suivantes :
    • id SERIAL PRIMARY KEY (clé primaire auto-incrémentée)
    • client_id INT
    • produit_id INT
    • date_commande DATE

• 5. Insertion de données aléatoires:

  • Insérer 100 000 clients avec des noms et des adresses e-mail aléatoires.
  • Insérer 1 000 produits avec des noms et des prix aléatoires.
  • Insérer 1 000 000 commandes avec des informations sur les clients, les produits et la date.
  • Utiliser les fonctions md5(random()::text) et random() * 100 pour générer des données aléatoires.
  • L'insertion des dates de commande est déterminée à partir de 2023-01-01 + un nombre aléatoire de jours entre 0 et 365.

Exercice 1: Analyse des Requêtes Lentes

• Objectif: Identifier et optimiser une requête lente.

• 1. Activation des logs pour les requêtes lentes:

  • Configurer PostgreSQL pour enregistrer les requêtes dont la durée d'exécution dépasse 500 millisecondes avec SET log min duration statement = 500;

• 2. Exécution d'une requête lente:

  • Exécuter la requête SELECT FROM commandes WHERE date_commande > '2023-01-01'; pour tester la performance.

Exercice 2: Utilisation des Index

• Objectif: Améliorer les performances des recherches en utilisant des index.

• 1. Création de l'index sur la colonne nom de la tableclients :

  • Créer un index idx_clients_nom sur la colonne nom de la table clients avec la commande CREATE INDEX idx_clients_nom ON clients (nom);

• 2. Exécution de la requête et analyse du plan d'exécution:

  • Exécuter la requête SELECT * FROM clients WHERE nom = '96a007831d3efbf4a066f031b50a520'; et analyser la requête pour voir comment des améliorations peuvent amener à des optimisations.

Exercice 3 : Utilisation des Index Combinés

• Objectif: Optimiser les recherches multi-colonnes avec des index combinés.

• 1. Création d'un index combiné sur les colonnes client_id et date_commande de la table commandes :

  • Créer un index idx_commandes_client_date sur les colonnes client_idet date_commande de la table commandes avec la commande CREATE INDEX idx_commandes_client_date ON commandes (client_id, date_commande);

• 2. Exécution de la requête et analyse de la requête :

  • Exécuter la requête EXPLAIN ANALYZE SELECT * FROM commandes WHERE client_id = 12345 AND date_commande > '2023-01-01'; et analyser le résultat.

Exercice 4 : Index Partiel

• Objectif: optimiser les performances en utilisant des index partiels.

• 1. Création d'un index partiel sur la colonne prix de la table produits :

• Créer un index idx_produits_prix sur la colonne prix de la table produits uniquement pour les valeurs prix > 50 avec la commande CREATE INDEX idx_produits_prix ON produits (prix) WHERE prix > 50;

• 2. Exécution et analyse de la requête :

  • Exécuter la requête EXPLAIN ANALYZE SELECT * FROM produits WHERE prix > 50; et faire l'analyse correspondante.

Exercice 5 : Optimisation des Jointures

• Objectif: Améliorer les jointures avec des index.

• 1. Création des index sur les colonnes de jointure :

  • Créer des index idx_commandes_client_id et idx_commandes_produit_id sur les colonnes client_id et produit_id de la table commandes.

Exercice 6 : Réduire les Scans Séquentiels

• Objectif: Minimiser les accès disques.

• 1. Identification des requêtes lentes :

  • Utiliser EXPLAIN ANALYZE SELECT * FROM commandes WHERE client_id = 12345; pour analyser les requêtes afin d'identifier des scans séquentiels.

• 2. Création des index nécessaires :

  • Créer un index sur la colonne client_id pour optimiser les requêtes qui utilisent cette colonne.

Exercice 7 : Utiliser EXPLAIN et EXPLAIN ANALYZE

• Objectif: Comprendre les plans d'exécution des requêtes pour les optimiser

• 1. Utilisation de EXPLAIN :

• Utilisez EXPLAIN SELECT * FROM commandes WHERE produit_id = 567; pour obtenir le plan d'exécution estimé.