Utiliser et créer des index

Améliorer les performances des requêtes dans PostgreSQL

Amy McCarty

Instructor

Aperçu des index

Quoi

  • Méthode pour créer des clés triées sur des colonnes afin d'accélérer la recherche
  • Similaire à l'index d'un livre
  • Référence à l'emplacement des données

Pourquoi

  • Requêtes plus rapides

Où

  • Colonnes souvent utilisées en filtre
  • Clé primaire
Améliorer les performances des requêtes dans PostgreSQL

Exemple d'index

ingrédient recette
tomates spaghetti & meatballs
oignons verts fried rice
œufs fried rice
bœuf haché spaghetti & meatballs
pâtes spaghetti & meatballs
riz fried rice
sauce soja fried rice 
SELECT *
FROM cookbook
WHERE recipe = 'fried rice'
Améliorer les performances des requêtes dans PostgreSQL

Index : clé et pointeur

Deux tables. Une table d'index contient la colonne recipe ainsi qu'une colonne de pointeurs, allant de _12 à _18 séquentiellement. La table avec index est la même table que celle de gauche avec une colonne de pointeurs en plus. Les valeurs de cette colonne de pointeurs sont identiques à celles de la table Index. Des flèches montrent comment les quatre valeurs pour fried rice dans la table Index correspondent aux quatre valeurs pour fried rice dans la table avec index.

Améliorer les performances des requêtes dans PostgreSQL

Trouver les index existants

pg_tables
  • Similaire à information_schema
    • spécifique à Postgres
  • Métadonnées sur la base
Améliorer les performances des requêtes dans PostgreSQL

Trouver les index existants

pg_tables
  • Similaire à information_schema
    • spécifique à Postgres
  • Métadonnées sur la base

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
Améliorer les performances des requêtes dans PostgreSQL

Créer un index

Table dinner avec les colonnes ingredient, recipe et serving size. tomatoes - spaghetti & meatballs - 4. green onions - fried rice - 2. eggs - fried rice - 2. ground beef - spaghetti & meatballs - 4. pasta - spaghetti & meatballs - 4. rice - fried rice - 2. soy sauce - fried rice - 2.

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);
Améliorer les performances des requêtes dans PostgreSQL

Utiliser ou non un index

Quand utiliser un index

  • Grandes tables
  • Conditions de filtre fréquentes
  • Clé primaire

Quand éviter un index

  • Petites tables
  • Colonnes avec beaucoup de null
  • Tables fréquemment mises à jour
    • L'index se fragmente
    • Écrit les données à deux endroits
Améliorer les performances des requêtes dans PostgreSQL

Tables fréquemment mises à jour

Deux tables. Une table d'index contient la colonne recipe ainsi qu'une colonne de pointeurs, allant de _12 à _19 séquentiellement. La table avec index est la même table que celle de gauche avec une colonne de pointeurs en plus. Les valeurs de cette colonne de pointeurs sont identiques à celles de la table Index. Des flèches montrent comment les quatre valeurs pour spaghetti and meatballs dans la table Index correspondent aux quatre valeurs pour spaghetti and meatballs dans la table avec index. Trois valeurs sont groupées en haut de la table Index. Une ligne spaghetti and meatballs est en bas de la table Index, correspondant à la nouvelle entrée basil dans la table avec index.

Améliorer les performances des requêtes dans PostgreSQL

Évaluer une requête avec index

Planificateur de requêtes

Plusieurs chefs autour d'une grande marmite 

EXPLAIN
SELECT * 
FROM cookbook

 

Plan d'exécution

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • Estimations de coût (temps)
Améliorer les performances des requêtes dans PostgreSQL

Passons à la pratique !

Améliorer les performances des requêtes dans PostgreSQL

Preparing Video For Download...