Les Index

Les types d'index

Type d’index	Description	Cas d’usage typique
B-tree	Structure équilibrée par défaut, idéale pour les requêtes d’égalité et de plage.	Clés primaires, colonnes fréquemment interrogées avec `=`, `>`, `<`, `BETWEEN`.
Hash	Basé sur une fonction de hachage, très rapide pour les recherches d’égalité exacte.	Colonnes utilisées uniquement avec `=` (pas de tri ou de plage).
GiST	Index généralisé pour les recherches, supporte les opérateurs personnalisés.	Données géométriques, texte en plein texte, types personnalisés.
SP-GiST	Variante de GiST pour les données non équilibrées (arbres de suffixes, etc.).	Données hiérarchiques, adresses IP, types de données non structurées.
GIN	Inversé par rapport à GiST, efficace pour les valeurs composites (tableaux, JSON, texte).	Colonnes contenant des tableaux, documents JSON, recherche de mots-clés.
BRIN	Index minimaliste pour les tables très grandes et ordonnées physiquement.	Tables volumineuses avec un ordre naturel (ex : données temporelles).
Bloom	Filtre probabiliste pour réduire le nombre de lignes à scanner.	Tables avec de nombreuses colonnes interrogées de manière aléatoire.

Index B-tree

sql

-- Création d'une table utilisateurs
CREATE TABLE utilisateurs (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100),
    email VARCHAR(100),
    date_inscription TIMESTAMP
);

-- Création d'un index B-tree sur la colonne 'email'
CREATE INDEX idx_utilisateurs_email ON utilisateurs (email);

-- Création d'un index B-tree sur plusieurs colonnes (index composite)
CREATE INDEX idx_utilisateurs_nom_email ON utilisateurs (nom, email);

Explications:

CREATE INDEX idx_utilisateurs_email ON utilisateurs (email); crée un index B-tree sur la colonne email.
PostgreSQL utilise B-tree par défaut, donc tu n’as pas besoin de préciser USING btree.
Les index composites (sur plusieurs colonnes) sont utiles pour les requêtes qui filtrent sur plusieurs champs.

Index Hash

Ci dessous quelques exemples d’utilisation des index HASH sous PostgreSQL, avec des cas concrets et des bonnes pratiques:

Création d’un index HASH sur une colonne simple

Supposons une table clients avec une colonne email souvent utilisée dans des requêtes de type WHERE email = '...'.

sql

CREATE INDEX idx_clients_email_hash ON clients USING HASH (email);

Utilité

L’index HASH est très efficace pour les recherches d’égalité (=), mais pas pour les comparaisons de plage (>, <, BETWEEN).

Utilisation dans une requête

L’index sera automatiquement utilisé par le planificateur de requêtes si la condition est une égalité.

sql

SELECT * FROM clients WHERE email = 'client@example.com';

Remarque

PostgreSQL utilise l’index HASH uniquement pour les opérateurs = et IN (si les valeurs sont constantes).

Index HASH sur plusieurs colonnes

Vous pouvez créer un index HASH sur plusieurs colonnes, mais il ne sera utilisé que si toutes les colonnes sont spécifiées dans la condition.

sql

CREATE INDEX idx_clients_nom_prenom_hash ON clients USING HASH (nom, prenom);

Exemple de requête utilisant l’index :

sql

SELECT * FROM clients WHERE nom = 'Dupont' AND prenom = 'Jean';

Cas d’usage typique : tables de jointure

Les index HASH sont souvent utilisés pour les colonnes de jointure, surtout si les valeurs sont uniformément distribuées.

sql

CREATE INDEX idx_commandes_client_id_hash ON commandes USING HASH (client_id);

Exemple de jointure :

sql

SELECT c.nom, co.montant
FROM clients c
JOIN commandes co ON c.id = co.client_id
WHERE co.client_id = 123;

Limites et bonnes pratiques

Pas de tri : Les index HASH ne permettent pas de trier les résultats (ORDER BY).
Pas de recherche de plage : Ils ne sont pas adaptés pour >, <, BETWEEN.
Maintenance : Les index HASH doivent être recréés après un VACUUM FULL ou une réorganisation majeure de la table.
PostgreSQL 10+ : Avant PostgreSQL 10, les index HASH n’étaient pas persistants après un redémarrage du serveur.

Vérification de l’utilisation de l’index

Pour vérifier si PostgreSQL utilise bien votre index HASH, utilisez EXPLAIN ANALYZE :

sql

EXPLAIN ANALYZE SELECT * FROM clients WHERE email = 'client@example.com';

Résultat attendu

Si l’index est utilisé, vous verrez une ligne comme Index Scan using idx_clients_email_hash on clients.

Quand ne pas utiliser un index HASH ?

Si vous avez besoin de recherches de plage ou de tri.
Si la colonne a une faible cardinalité (peu de valeurs distinctes).
Si vous utilisez souvent des opérateurs autres que =.

Index GIST

Ci-dessous des exemples concrets d’utilisation des index GiST (Generalized Search Tree) sous PostgreSQL, avec des cas d’usage typiques et des bonnes pratiques.

Index GiST pour les types géométriques (PostGIS)

L’usage le plus courant des index GiST est pour les requêtes spatiales avec l’extension PostGIS.

Exemple : Table avec des points géographiques

sql

CREATE EXTENSION postgis;
CREATE TABLE lieux (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100),
    position GEOMETRY(Point, 4326)
);

-- Création d'un index GiST sur la colonne géométrique
CREATE INDEX idx_lieux_position_gist ON lieux USING GIST(position);

Requête utilisant l’index :

sql

-- Trouver les lieux à moins de 10 km d'un point donné
SELECT nom
FROM lieux
WHERE ST_DWithin(position, ST_SetSRID(ST_MakePoint(2.3522, 48.8566), 4326), 10000);

INFO

L’index GiST accélère les calculs de distance et les intersections spatiales.

Index GiST pour les types de données textuels (recherche full-text)

GiST peut aussi être utilisé pour la recherche full-text, bien que GIN soit souvent plus efficace.

sql

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    contenu TEXT
);
-- Création d'un index GiST pour la recherche full-text
CREATE INDEX idx_documents_contenu_gist ON documents USING GIST(to_tsvector('french', contenu));

Requête utilisant l’index :

sql

SELECT id, contenu
FROM documents
WHERE to_tsvector('french', contenu) @@ to_tsquery('french', 'PostgreSQL & index');

INFO

L’index GiST permet d’accélérer les recherches de mots-clés.

Index GiST pour les types de données personnalisés

GiST permet d’indexer des types de données personnalisés, comme des intervalles, des réseaux, etc.

Exemple : Index sur des intervalles de dates

sql

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    periode TSRANGE
);
-- Création d'un index GiST sur un type intervalle
CREATE INDEX idx_reservations_periode_gist ON reservations USING GIST(periode);

Requête utilisant l’index :

sql

-- Trouver les réservations qui chevauchent une période donnée
SELECT id
FROM reservations
WHERE periode && '[2025-09-01, 2025-09-30]'::TSRANGE;

INFO

L’index GiST accélère les tests de chevauchement (&&), de contenu (@>), etc.

Index GiST pour les types de données géométriques natifs

Même sans PostGIS, PostgreSQL supporte des types géométriques de base.

sql

CREATE TABLE formes (
    id SERIAL PRIMARY KEY,
    rectangle BOX
);
-- Création d'un index GiST sur un type BOX
CREATE INDEX idx_formes_rectangle_gist ON formes USING GIST(rectangle);

Requête utilisant l’index :

sql

-- Trouver les rectangles qui contiennent un point donné
SELECT id
FROM formes
WHERE rectangle @> '(10,10)'::POINT;

Vérification de l’utilisation de l’index GiST

Pour vérifier si PostgreSQL utilise bien votre index GiST, utilisez EXPLAIN ANALYZE:

sql

EXPLAIN ANALYZE
SELECT * FROM lieux
WHERE ST_DWithin(position, ST_SetSRID(ST_MakePoint(2.3522, 48.8566), 4326), 10000);

INFO

Cherchez Index Scan using idx_lieux_position_gist dans le plan d’exécution.

Quand utiliser GiST plutôt que GIN ou SP-GiST ?

GiST : Idéal pour les données géométriques, les intervalles, les recherches de chevauchement.
GIN : Mieux adapté pour les recherches full-text, les tableaux, les données composites.
SP-GiST : Utile pour les données hiérarchiques ou les recherches de préfixe.

Index GIN

Voici des exemples concrets d’utilisation des index GIN (Generalized Inverted Index) sous PostgreSQL, avec des cas d’usage typiques et des bonnes pratiques.

Index GIN pour les colonnes de type tableau (ARRAY)

Les index GIN sont très efficaces pour rechercher des valeurs dans des colonnes de type tableau.

Exemple : Table avec un tableau de tags

sql

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    titre VARCHAR(255),
    tags TEXT[]
);
-- Création d'un index GIN sur la colonne tags
CREATE INDEX idx_articles_tags_gin ON articles USING GIN(tags);

Requêtes utilisant l’index :

sql

-- Trouver les articles qui ont le tag 'postgresql'
SELECT * FROM articles WHERE 'postgresql' = ANY(tags);

-- Trouver les articles qui ont tous les tags 'postgresql' ET 'index'
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'index'];

-- Trouver les articles qui ont au moins un des tags 'postgresql' OU 'base de données'
SELECT * FROM articles WHERE tags && ARRAY['postgresql', 'base de données'];

INFO

L’index GIN accélère les recherches de valeurs dans les tableaux, surtout pour les opérateurs @>, && et = ANY.

Index GIN pour la recherche full-text

GIN est souvent utilisé pour la recherche full-text, car il est plus rapide que GiST pour ce type de requête.

sql

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    contenu TEXT
);
-- Création d'un index GIN pour la recherche full-text
CREATE INDEX idx_documents_contenu_gin ON documents USING GIN(to_tsvector('french', contenu));

Requêtes utilisant l’index :

sql

-- Trouver les documents contenant les mots 'postgresql' ET 'index'
SELECT id, contenu
FROM documents
WHERE to_tsvector('french', contenu) @@ to_tsquery('french', 'postgresql & index');

-- Trouver les documents contenant la phrase 'optimisation des requêtes'
SELECT id, contenu
FROM documents
WHERE to_tsvector('french', contenu) @@ plainto_tsquery('french', 'optimisation des requêtes');

INFO

L’index GIN est très efficace pour les recherches de mots-clés et les requêtes booléennes.

Index GIN pour les colonnes de type JSON/JSONB

GIN est le type d’index le plus performant pour les requêtes sur des colonnes JSONB, surtout pour rechercher des clés ou des valeurs imbriquées.

sql

CREATE TABLE produits (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(255),
    attributs JSONB
);
-- Création d'un index GIN sur la colonne JSONB
CREATE INDEX idx_produits_attributs_gin ON produits USING GIN(attributs);

Requêtes utilisant l’index :

sql

-- Trouver les produits dont l'attribut 'couleur' est 'bleu'
SELECT * FROM produits WHERE attributs @> '{"couleur": "bleu"}';

-- Trouver les produits qui ont une clé 'prix' dans leurs attributs
SELECT * FROM produits WHERE attributs ? 'prix';

-- Trouver les produits dont la valeur de la clé 'prix' est supérieure à 100
SELECT * FROM produits WHERE attributs->>'prix'::NUMERIC > 100;

INFO

→ L’index GIN accélère les recherches de clés, de valeurs et les tests de contenu dans les JSONB.

Index GIN pour les colonnes de type composite ou personnalisé

GIN peut aussi être utilisé pour des types de données composites ou personnalisés, comme les hstore ou les ltree.

Exemple avec hstore

sql

CREATE EXTENSION hstore;
CREATE TABLE profils (
    id SERIAL PRIMARY KEY,
    infos HSTORE
);
-- Création d'un index GIN sur la colonne hstore
CREATE INDEX idx_profils_infos_gin ON profils USING GIN(infos);

Requêtes utilisant l’index :

sql

-- Trouver les profils dont la clé 'langue' a la valeur 'français'
SELECT * FROM profils WHERE infos @> 'langue=>français';

-- Trouver les profils qui ont une clé 'pays'
SELECT * FROM profils WHERE infos ? 'pays';

Vérification de l’utilisation de l’index GIN

Pour vérifier si PostgreSQL utilise bien votre index GIN, utilisez EXPLAIN ANALYZE :

sql

EXPLAIN ANALYZE
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'index'];

TIP

Cherchez Bitmap Heap Scan ou Index Scan avec le nom de votre index GIN dans le plan d’exécution.

Quand utiliser GIN plutôt que GiST ou SP-GiST ?

GIN : Idéal pour les tableaux, les JSONB, la recherche full-text, les données composites.
GiST : Mieux adapté pour les données géométriques, les intervalles, les recherches de chevauchement.
SP-GiST : Utile pour les données hiérarchiques ou les recherches de préfixe.

Index BRIN

Voici des exemples concrets d’utilisation des index BRIN (Block Range INdex) sous PostgreSQL, avec des cas d’usage typiques, des bonnes pratiques et des exemples de requêtes.

Qu’est-ce qu’un index BRIN ?

BRIN est un type d’index conçu pour les très grandes tables où les données sont physiquement ordonnées selon une colonne (par exemple, une colonne de type date, timestamp, ou série numérique).
Il est très compact et rapide à maintenir, mais moins précis qu’un B-tree pour les recherches ponctuelles.
Idéal pour les tables où les données sont insérées dans l’ordre (logs, séries temporelles, etc.).

Création d’un index BRIN sur une colonne de type timestamp

Supposons une table de logs où les entrées sont toujours insérées par ordre chronologique :

sql

CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    date_log TIMESTAMPTZ NOT NULL,
    message TEXT
);

-- Création d'un index BRIN sur la colonne date_log
CREATE INDEX idx_logs_date_brin ON logs USING BRIN(date_log);

Pourquoi BRIN ?

Les logs sont toujours insérés par ordre chronologique.
La table peut devenir très volumineuse.
BRIN est très efficace pour les requêtes de plage sur date_log.

Requêtes utilisant l’index BRIN

Exemple 1 : Recherche de plage de dates

sql

-- Trouver tous les logs entre deux dates
SELECT * FROM logs WHERE date_log BETWEEN '2025-09-01' AND '2025-09-16';

INFO

L’index BRIN permet de sauter rapidement les blocs de données qui ne contiennent pas de logs dans cette plage.

Exemple 2 : Recherche de logs récents

sql

-- Trouver les logs des dernières 24 heures
SELECT * FROM logs WHERE date_log > NOW() - INTERVAL '24 hours';

INFO

BRIN est très efficace pour ce type de requête sur des données ordonnées.

Création d’un index BRIN sur une colonne numérique ordonnée

Exemple avec une table de mesures de capteurs, où les données sont insérées par ordre de timestamp et de valeur :

sql

CREATE TABLE mesures (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL,
    valeur DOUBLE PRECISION NOT NULL
);
-- Création d'un index BRIN sur la colonne timestamp
CREATE INDEX idx_mesures_timestamp_brin ON mesures USING BRIN(timestamp);

Requête utilisant l’index :

sql

-- Trouver les mesures entre deux timestamps
SELECT * FROM mesures
WHERE timestamp BETWEEN '2025-09-01 00:00:00' AND '2025-09-01 12:00:00';

Création d’un index BRIN multi-colonnes

Vous pouvez créer un index BRIN sur plusieurs colonnes, si elles sont corrélées et ordonnées ensemble.

sql

-- Création d'un index BRIN sur deux colonnes
CREATE INDEX idx_mesures_timestamp_valeur_brin ON mesures USING BRIN(timestamp, valeur);

Remarque

BRIN est surtout efficace si les colonnes sont physiquement corrélées dans l’ordre d’insertion.

Vérification de l’utilisation de l’index BRIN

Pour vérifier si PostgreSQL utilise bien votre index BRIN, utilisez EXPLAIN ANALYZE :

sql

EXPLAIN ANALYZE
SELECT * FROM logs
WHERE date_log BETWEEN '2025-09-01' AND '2025-09-16';

INFO

Cherchez Index Scan using idx_logs_date_brin dans le plan d’exécution.

Quand utiliser BRIN plutôt que B-tree ou GiST ?

Critère	BRIN	B-tree	GiST
Données ordonnées	✅ Idéal	⚠️ Possible	❌ Non
Taille de l’index	Très compact	Volumineux	Compact
Recherche ponctuelle	❌ Peu précis	✅ Très précis	⚠️ Variable
Recherche de plage	✅ Très rapide	✅ Rapide	⚠️ Variable
Maintenance	Très rapide	Lente	Rapide

Cas d’usage typiques pour BRIN

Tables de logs, séries temporelles, données IoT.
Tables où les données sont toujours insérées dans l’ordre (par exemple, par date).
Tables très volumineuses où la taille de l’index est un critère important.

Bonnes pratiques avec BRIN

Ordonner les données : BRIN est efficace si les données sont physiquement ordonnées selon la colonne indexée.
Éviter les mises à jour aléatoires : BRIN est optimisé pour les insertions séquentielles.
Utiliser pages_per_range : Vous pouvez ajuster la taille des blocs indexés pour optimiser les performances (par défaut, 128 pages par bloc).

sql

CREATE INDEX idx_logs_date_brin ON logs USING BRIN(date_log) WITH (pages_per_range = 64);

Use the index Luke

Le site Use the index, Luke décrit les problématiques du SQL, et vous donnent des pistes pour créer des indexes efficaces en fonctions de vos cas de figures.

Les Index ​

Les types d'index ​

Index B-tree ​

Index Hash ​

Création d’un index HASH sur une colonne simple ​

Utilisation dans une requête ​

Index HASH sur plusieurs colonnes ​

Cas d’usage typique : tables de jointure ​

Limites et bonnes pratiques ​

Vérification de l’utilisation de l’index ​

Quand ne pas utiliser un index HASH ? ​

Index GIST ​

Index GiST pour les types géométriques (PostGIS) ​

Exemple : Table avec des points géographiques ​

Index GiST pour les types de données textuels (recherche full-text) ​

Index GiST pour les types de données personnalisés ​

Exemple : Index sur des intervalles de dates ​

Index GiST pour les types de données géométriques natifs ​

Vérification de l’utilisation de l’index GiST ​

Quand utiliser GiST plutôt que GIN ou SP-GiST ? ​

Index GIN ​

Index GIN pour les colonnes de type tableau (ARRAY) ​

Exemple : Table avec un tableau de tags ​

Index GIN pour la recherche full-text ​

Index GIN pour les colonnes de type JSON/JSONB ​

Index GIN pour les colonnes de type composite ou personnalisé ​

Exemple avec hstore ​

Vérification de l’utilisation de l’index GIN ​

Quand utiliser GIN plutôt que GiST ou SP-GiST ? ​

Index BRIN ​

Qu’est-ce qu’un index BRIN ? ​

Création d’un index BRIN sur une colonne de type timestamp ​

Requêtes utilisant l’index BRIN ​

Exemple 1 : Recherche de plage de dates ​

Exemple 2 : Recherche de logs récents ​

Création d’un index BRIN sur une colonne numérique ordonnée ​

Création d’un index BRIN multi-colonnes ​

Vérification de l’utilisation de l’index BRIN ​

Quand utiliser BRIN plutôt que B-tree ou GiST ? ​

Bonnes pratiques avec BRIN ​

Use the index Luke ​

Articles divers ​

Les Index

Les types d'index

Index B-tree

Index Hash

Création d’un index HASH sur une colonne simple

Utilisation dans une requête

Index HASH sur plusieurs colonnes

Cas d’usage typique : tables de jointure

Limites et bonnes pratiques

Vérification de l’utilisation de l’index

Quand ne pas utiliser un index HASH ?

Index GIST

Index GiST pour les types géométriques (PostGIS)

Exemple : Table avec des points géographiques

Index GiST pour les types de données textuels (recherche full-text)

Index GiST pour les types de données personnalisés

Exemple : Index sur des intervalles de dates

Index GiST pour les types de données géométriques natifs

Vérification de l’utilisation de l’index GiST

Quand utiliser GiST plutôt que GIN ou SP-GiST ?

Index GIN

Index GIN pour les colonnes de type tableau (ARRAY)

Exemple : Table avec un tableau de tags

Index GIN pour la recherche full-text

Index GIN pour les colonnes de type JSON/JSONB

Index GIN pour les colonnes de type composite ou personnalisé

Exemple avec hstore

Vérification de l’utilisation de l’index GIN

Quand utiliser GIN plutôt que GiST ou SP-GiST ?

Index BRIN

Qu’est-ce qu’un index BRIN ?

Création d’un index BRIN sur une colonne de type timestamp

Requêtes utilisant l’index BRIN

Exemple 1 : Recherche de plage de dates

Exemple 2 : Recherche de logs récents

Création d’un index BRIN sur une colonne numérique ordonnée

Création d’un index BRIN multi-colonnes

Vérification de l’utilisation de l’index BRIN

Quand utiliser BRIN plutôt que B-tree ou GiST ?

Bonnes pratiques avec BRIN

Use the index Luke

Articles divers