La croissance des volumes de données dans les systèmes d'information entreprise entraîne souvent une dégradation progressive des performances des requêtes SQL. Ces ralentissements peuvent affecter la réactivité des interfaces et, dans les cas critiques, perturber les processus métier fondamentaux. Avec des bases de données comme KingbaseES, les gains de performence proviennent principalement de l'optimisation des requêtes, de la conception des index et de la configuration des paramètres, et non d'une limitation intrinsèque du système.

Optimisation par les index : le fondement

La sélection et la création d'index appropriés représentent le levier d'optimisation le plus direct et le plus efficace. Un index bien conçu permet d'éviter des parcours complets de tables, mais une indexation excessive peut dégrader les performances des opérations d'écriture.

Choix du type d'index

Chaque type d'index répond à des scénarios d'interrogation spécifiques.

Index B-tree

C'est l'index par défaut, basé sur la structure B+tree. Il est optimal pour les recherches par égalité, par plage, le tri et les agrégations MIN/MAX. Il convient à la majorité des cas d'usage transactionnels.

-- Création d'une table et insertion de données de test
CREATE TABLE produits (id_produit SERIAL, designation VARCHAR(200));
INSERT INTO produits (designation) SELECT 'Article_' || generate_series(1, 200000);
-- Création de l'index B-tree
CREATE INDEX idx_produits_id ON produits USING btree(id_produit);
-- Utilisation efficace pour une recherche par plage
EXPLAIN ANALYZE SELECT * FROM produits WHERE id_produit < 100;

Index Hash

Il offre des performances optimales pour les recherches par stricte égalité, mais il est inutilisable pour les plages, les tris ou les comparaisons. Son utilisation doit être limitée à des cas très spécifiuqes.

CREATE TABLE sessions (id_session INT, donnee TEXT);
INSERT INTO sessions SELECT i, md5(i::text) FROM generate_series(1, 150000) i;
CREATE INDEX idx_sessions_hash ON sessions USING hash(id_session);
-- Recherche par égalité : très performante
EXPLAIN ANALYZE SELECT * FROM sessions WHERE id_session = 500;
-- Recherche par plage : ignore l'index, scan séquentiel
EXPLAIN ANALYZE SELECT * FROM sessions WHERE id_session < 500;

Index Bitmap

Idéal pour les colonnes à faible cardinalité (ex: statut, catégorie). Il excelle dans les requêtes OLAP avec des conditions AND/OR multiples sur de grands volumes de données.

CREATE TABLE evenements (type_evt INT, severite INT);
INSERT INTO evenements SELECT round(random()*5), round(random()*100) FROM generate_series(1, 1000000);
CREATE INDEX idx_evt_bitmap ON evenements USING bitmap(type_evt);
ANALYZE evenements;
-- Requête combinée avec de faibles sélectivités
EXPLAIN ANALYZE SELECT count(*) FROM evenements WHERE type_evt = 2 AND severite > 80;

Index GIN

L'index inversé généralisé est conçu pour les types de données composites comme les tableaux, les documents JSON ou pour la recherche plein texte.

CREATE TABLE articles_blog (id INT, contenu TEXT);
INSERT INTO articles_blog SELECT i, 'Contenu technique de l article ' || i FROM generate_series(1, 50000) i;
CREATE INDEX idx_articles_gin ON articles_blog USING gin(to_tsvector('french', contenu));
-- Recherche textuelle efficace
EXPLAIN ANALYZE SELECT * FROM articles_blog WHERE to_tsvector('french', contenu) @@ plainto_tsquery('optimisation');

Index BRIN

L'index de plage de blocs est compact et efficace pour les données physiquement ordonnées selon la colonne indexée, comme les séries temporelles ou les identifiants auto-incrémentés.

CREATE TABLE journal_operations (id_op BIGINT, horodat TIMESTAMP DEFAULT now(), description TEXT);
INSERT INTO journal_operations (id_op, description) SELECT generate_series(1, 500000), 'Opération standard';
CREATE INDEX idx_journal_brin ON journal_operations USING brin(id_op);
-- Recherche sur une plage d'identifiants consécutifs
EXPLAIN ANALYZE SELECT * FROM journal_operations WHERE id_op BETWEEN 1000 AND 1500;

Techniques avancées d'indexation

Index sur expression

Lorsqu'une requête applique une fonction sur une colonne dans sa clause WHERE, l'index sur cette colonne n'est pas utilisé. Il faut créer un index sur l'expression fonctionnelle.

CREATE TABLE clients (id_client INT, nom_complet TEXT);
-- Index pour des recherches insensibles à la casse
CREATE INDEX idx_clients_upper ON clients(UPPER(nom_complet));
EXPLAIN ANALYZE SELECT * FROM clients WHERE UPPER(nom_complet) = 'MARTIN DUPONT';

Index composite et règle de la gauche la plus longue

Pour un index sur plusieurs colonnes, la requête doit filtrer sur les colonnes de gauche (dans l'ordre de définition de l'index) pour pouvoir l'utiliser.

CREATE TABLE ventes (id_produit INT, id_magasin INT, date_vente DATE, montant DECIMAL);
CREATE INDEX idx_ventes_composite ON ventes(id_produit, id_magasin, date_vente);
-- Utilisation de l'index : filtre sur la première colonne
EXPLAIN ANALYZE SELECT * FROM ventes WHERE id_produit = 42;
-- Utilisation partielle : filtre sur les deux premières colonnes
EXPLAIN ANALYZE SELECT * FROM ventes WHERE id_produit = 42 AND id_magasin = 3;
-- Pas d'utilisation : filtre sur la seconde colonne uniquement
EXPLAIN ANALYZE SELECT * FROM ventes WHERE id_magasin = 3;

Index partiel

Créer un index sur un sous-ensemble de lignes (avec une clause WHERE) réduit sa taille et les coûts de maintenance, si les requêtes ciblent systématiquement ce sous-ensemble.

CREATE TABLE comptes_actifs (id_compte INT, solde DECIMAL, statut VARCHAR(10));
CREATE INDEX idx_comptes_actifs_solde ON comptes_actifs(solde) WHERE statut = 'ACTIF';
-- La requête bénéficie de l'index partiel
EXPLAIN ANALYZE SELECT * FROM comptes_actifs WHERE statut = 'ACTIF' AND solde > 1000;

Maintenance des index

La surveillance et la maintenance régulières sont essentielles pour conserver les performances.

-- Identifier les index inutilisés
SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;
-- Reconstruire un index fragmenté après une volumineuse suppression
REINDEX INDEX idx_produits_id;
-- Mettre à jour les statistiques du planificateur
ANALYZE produits;

Analyse et pilotage du plan d'exécution

Comprendre le plan d'exécution généré par l'optimiseur est crucial pour diagnostiquer les lenteurs. La commande EXPLAIN ANALYZE exécute la requête et fournit des métriques réelles.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.designation, COUNT(v.id_produit)
FROM produits p
LEFT JOIN ventes v ON p.id_produit = v.id_produit
WHERE p.id_produit > 1000
GROUP BY p.designation;

Les éléments clés à vérifier sont : le type de scan (sequential, index), la méthode de jointure (hash, merge, nested loop), l'écart entre les lignes estimées et réelles, et l'utilisation de disque (temp files).

Intervention manuelle avec les HINTs

Si l'optimiseur fait un choix sous-optimal persistant malgré des statistiques à jour, des HINTs peuvent guider son choix. Cette technique doit être utilisée avec parcimonie.

-- Forcer l'utilisation d'un index spécifique
EXPLAIN SELECT /*+ IndexScan(produits idx_produits_id) */ * FROM produits WHERE id_produit > 10000;
-- Forcer une méthode de jointure
EXPLAIN SELECT /*+ HashJoin(produits ventes) */ * FROM produits p JOIN ventes v ON p.id_produit = v.id_produit;

Optimisation paramétrique et parallélisme

Les paramètres de configuration de la base de données doivent être adaptés aux ressources matérielles et au type de charge de travail.

-- Ajuster la mémoire dédiée au tri (par session pour test)
SET work_mem = '64MB';
-- Augmenter le nombre maximal de processus workers pour le parallélisme
ALTER SYSTEM SET max_worker_processes = 16;
-- Définir le nombre de workers par requête
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
-- Adapter le coût des accès disque pour les SSD
ALTER SYSTEM SET random_page_cost = 1.5; -- Défaut souvent 4.0

L'activation de l'exécution parallèle permet de répartir le travail sur pluiseurs cœurs CPU pour les requêtes analytiques sur de grandes tables. L'efficacité dépend de la granularité des données et de la capacité à partitionner le travail.

Réécriture de requêtes et structures précalculées

Une reformulation intelligente d'une requête peut apporter des gains spectaculaires.

-- Remplacer UNION par UNION ALL quand les doublons ne sont pas possibles ou gérés autrement
SELECT id_client FROM commandes_2022 UNION ALL SELECT id_client FROM commandes_2023;
-- Privilégier une jointure à une sous-requête corrélée
-- Requête lente avec sous-requête corrélée
SELECT c.nom, (SELECT COUNT(*) FROM commandes cmd WHERE cmd.id_client = c.id_client) FROM clients c;
-- Requête optimisée avec jointure
SELECT c.nom, COUNT(cmd.id_commande) AS nb_commandes
FROM clients c LEFT JOIN commandes cmd ON c.id_client = cmd.id_client
GROUP BY c.id_client, c.nom;

Vues matérialisées

Pour des requêtes complexes et coûteuses (rapports, agrégations) dont les données sources changent rarement, une vue matérialisée stocke le résultat physiquement.

CREATE MATERIALIZED VIEW mv_resume_ventes AS
SELECT p.designation, EXTRACT(YEAR FROM v.date_vente) AS annee, SUM(v.montant) AS chiffre_affaires
FROM produits p JOIN ventes v ON p.id_produit = v.id_produit
GROUP BY p.designation, EXTRACT(YEAR FROM v.date_vente);
-- Pour mettre à jour les données
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_resume_ventes;

Monitoring et outillage

Une optimisation pérenne repose sur un monitoring continu. Il faut activer les modules de statistiques étendues pour collecter les métriques sur les requêtes.

-- Activer l'extension de suivi (à placer dans la configuration initiale)
-- shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Identifier les requêtes les plus consommatrices
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Des outils intégrés ou tiers peuvent également fournir des recommandations automatiques sur la création d'index ou la réécriture de requêtes.