Retour au blog

Optimisation PostgreSQL : De 30s à 0.2s

20 Août 2024 15 min de lecture PostgreSQL

Comment j'ai optimisé un système de reporting lent en réécrivant des requêtes complexes. Techniques d'indexation, EXPLAIN ANALYZE et bonnes pratiques PostgreSQL.

Le défi : Requête de 30 secondes

Client : Entreprise de logistique (Marseille)

Problème : Rapport de ventes mensuel prenait 30 secondes à s'exécuter

Impact : Blocage de l'interface utilisateur, frustration des équipes

Analyse initiale avec EXPLAIN ANALYZE

La requête problématique :

SELECT 
    c.nom_client,
    p.nom_produit,
    SUM(v.quantite) as total_ventes,
    SUM(v.prix * v.quantite) as chiffre_affaires
FROM ventes v
JOIN clients c ON v.client_id = c.id
JOIN produits p ON v.produit_id = p.id
WHERE v.date_vente >= '2024-01-01'
    AND v.date_vente < '2024-02-01'
    AND c.region = 'PACA'
GROUP BY c.nom_client, p.nom_produit
ORDER BY chiffre_affaires DESC;

Résultat EXPLAIN ANALYZE :

Article Premium

Cet article contient des techniques avancées d'optimisation PostgreSQL, des requêtes SQL optimisées et des configurations de performance.

Ce que vous découvrirez :

  • Techniques d'indexation avancées
  • Requêtes SQL optimisées
  • Configuration PostgreSQL
  • Vues matérialisées
  • Monitoring et maintenance
  • Bonnes pratiques de performance
Demander l'article complet Voir mes compétences

Stratégie d'optimisation

1. Création d'index stratégiques

-- Index composite pour la requête principale
CREATE INDEX idx_ventes_date_client_produit 
ON ventes (date_vente, client_id, produit_id) 
INCLUDE (quantite, prix);

-- Index sur la région des clients
CREATE INDEX idx_clients_region 
ON clients (region) 
WHERE region = 'PACA';

-- Index sur les noms pour les JOINs
CREATE INDEX idx_clients_nom ON clients (id, nom_client);
CREATE INDEX idx_produits_nom ON produits (id, nom_produit);

2. Optimisation de la requête

-- Requête optimisée avec CTE
WITH ventes_paca AS (
    SELECT v.client_id, v.produit_id, v.quantite, v.prix
    FROM ventes v
    INNER JOIN clients c ON v.client_id = c.id
    WHERE v.date_vente >= '2024-01-01'
        AND v.date_vente < '2024-02-01'
        AND c.region = 'PACA'
)
SELECT 
    c.nom_client,
    p.nom_produit,
    SUM(vp.quantite) as total_ventes,
    SUM(vp.prix * vp.quantite) as chiffre_affaires
FROM ventes_paca vp
INNER JOIN clients c ON vp.client_id = c.id
INNER JOIN produits p ON vp.produit_id = p.id
GROUP BY c.nom_client, p.nom_produit
ORDER BY chiffre_affaires DESC;

3. Configuration PostgreSQL

-- Optimisation des paramètres
ALTER SYSTEM SET shared_buffers = '256MB';
ALTER SYSTEM SET effective_cache_size = '1GB';
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '64MB';

-- Redémarrage du service
SELECT pg_reload_conf();

Résultats obtenus

Nouveau EXPLAIN ANALYZE :

Techniques avancées utilisées

Index partiels

-- Index partiel pour les données récentes
CREATE INDEX idx_ventes_recentes 
ON ventes (date_vente, client_id) 
WHERE date_vente >= '2024-01-01';

Vues matérialisées

-- Vue matérialisée pour les rapports fréquents
CREATE MATERIALIZED VIEW mv_ventes_mensuelles AS
SELECT 
    DATE_TRUNC('month', date_vente) as mois,
    c.region,
    SUM(prix * quantite) as ca_total
FROM ventes v
JOIN clients c ON v.client_id = c.id
GROUP BY DATE_TRUNC('month', date_vente), c.region;

-- Index sur la vue matérialisée
CREATE INDEX idx_mv_ventes_mois_region 
ON mv_ventes_mensuelles (mois, region);

Analyse des statistiques

-- Mise à jour des statistiques
ANALYZE ventes;
ANALYZE clients;
ANALYZE produits;

-- Vérification des statistiques
SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
WHERE tablename IN ('ventes', 'clients', 'produits');

Bonnes pratiques PostgreSQL

  1. Index composites : Créer des index sur les colonnes utilisées dans WHERE et JOIN
  2. INCLUDE columns : Éviter les lookups supplémentaires
  3. Index partiels : Pour les requêtes sur des sous-ensembles
  4. Vues matérialisées : Pour les rapports complexes récurrents
  5. Configuration mémoire : Ajuster selon la RAM disponible

Monitoring continu

-- Requête pour identifier les requêtes lentes
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
WHERE mean_time > 1000  -- Plus de 1 seconde
ORDER BY mean_time DESC
LIMIT 10;

🚀 Besoin d'optimiser vos bases de données ?

Chaque base de données a ses spécificités. Contactez-moi pour une analyse personnalisée et des optimisations sur-mesure.

Discuter de votre projet