Utiliser et créer des index

Améliorer la performance des requêtes dans PostgreSQL

Amy McCarty

Instructor

Aperçu des index

Quoi

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

Pourquoi

  • Requêtes plus rapides

Où

  • Colonnes souvent filtrées
  • Clé primaire
Améliorer la performance des requêtes dans PostgreSQL

Exemple d'index

ingredient recipe
tomatoes spaghetti & meatballs
green onions fried rice
eggs fried rice
ground beef spaghetti & meatballs
pasta spaghetti & meatballs
rice fried rice
soy sauce fried rice 
SELECT *
FROM cookbook
WHERE recipe = 'fried rice'
Améliorer la performance des requêtes dans PostgreSQL

Index comme clé et pointeur

Deux tableaux. Un tableau d'index contient la colonne recipe et une colonne de pointeurs, de _12 à _18. Le tableau avec index est le même que celui de gauche avec une colonne de pointeurs supplémentaire. Les valeurs de cette colonne correspondent à celles du tableau d'index. Des flèches montrent comment les quatre valeurs « fried rice » dans le tableau d'index correspondent aux quatre valeurs « fried rice » dans le tableau avec index.

Améliorer la performance des requêtes dans PostgreSQL

Repérer les index existants

pg_tables
  • Semblable à information_schema
    • propre à Postgres
  • Métadonnées sur la base de données
Améliorer la performance des requêtes dans PostgreSQL

Repérer les index existants

pg_tables
  • Semblable à information_schema
    • propre à Postgres
  • Métadonnées sur la base de données

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
Améliorer la performance 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 la performance des requêtes dans PostgreSQL

Utiliser ou non un index

Quand utiliser un index

  • Grandes tables
  • Conditions de filtrage courantes
  • Clé primaire

À éviter

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

Tables souvent mises à jour

Deux tableaux. Un tableau d'index contient la colonne recipe et une colonne de pointeurs, de _12 à _19. Le tableau avec index est le même que celui de gauche avec une colonne de pointeurs supplémentaire. Les valeurs correspondent à celles du tableau d'index. Des flèches montrent comment les quatre valeurs « spaghetti & meatballs » dans le tableau d'index correspondent aux quatre valeurs « spaghetti & meatballs » dans le tableau avec index. Trois valeurs sont triées ensemble en haut du tableau d'index. Une valeur « spaghetti & meatballs » est en bas, correspondant à la nouvelle entrée basil dans le tableau avec index.

Améliorer la performance des requêtes dans PostgreSQL

Évaluer les requêtes avec index

Planificateur de requêtes

Plusieurs chefs autour d'un grand chaudron 

EXPLAIN
SELECT * 
FROM cookbook

 

Plan de requête

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

Passons à la pratique !

Améliorer la performance des requêtes dans PostgreSQL

Preparing Video For Download...