Cycle de vie d'une requête et planificateur

Améliorer les performances des requêtes dans PostgreSQL

Amy McCarty

Instructor

Cycle de vie basique d'une requête

Système Étapes front end Processus back end
1 Parser Envoi de la requête à la base Vérifie la syntaxe. Traduit le SQL en une syntaxe plus lisible par la machine selon des règles stockées.
2 Planner & Optimizer Évaluer et optimiser les tâches Utilise les stats pour créer un plan. Calcule les coûts et choisit le meilleur plan.
3 Executor Retourner les résultats Suit le plan pour exécuter la requête.
Améliorer les performances des requêtes dans PostgreSQL

Planificateur et optimiseur de requêtes

S'adapte aux changements de structure SQL

  • Génère des arbres de plan
    • Nœuds correspondant aux étapes
    • Visualiser avec EXPLAIN
  • Estime le coût de chaque arbre
    • Statistiques depuis pg_tables
    • Optimisation basée sur le temps
1 Plan tree : https://www.postgresql.org/docs/current/querytree.html
Améliorer les performances des requêtes dans PostgreSQL

Statistiques depuis pg_tables

SELECT * FROM pg_class
WHERE relname = 'mytable'

-- exemple de colonnes en sortie
| relname | relhasindex |

SELECT * FROM pg_stats
WHERE tablename = 'mytable'

-- exemple de colonnes en sortie
null_frac | avg_width | n_distinct |
  • Index de colonnes
  • Nombre de valeurs nulles
  • Largeur de colonne
  • Valeurs distinctes
Améliorer les performances des requêtes dans PostgreSQL

EXPLAIN

 

  • Vue sur le plan de requête
  • Étapes et estimations de coût
    • N'exécute pas la requête

 

  • Parcours séquentiel de la table cheeses
  • Estimations de coût et de taille

 

EXPLAIN
SELECT * FROM cheeses

 

Seq Scan on cheeses 
(cost=0.00..10.50 rows=5725 width=296)
Améliorer les performances des requêtes dans PostgreSQL

EXPLAIN : Scan

 

  • Étape du plan
  • Renvoie des lignes

 

Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)

 

 

 

 

  • Seq Scan : lecture de toutes les lignes de la table
Améliorer les performances des requêtes dans PostgreSQL

EXPLAIN : Coût

 

  • Sans dimension
  • Comparer des structures à sortie identique
    • Ne pas comparer des requêtes à sortie différente

 

Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)

 

 

 

 

 

  • 0.00.. : temps de démarrage

  • ..10.50 : temps total

  • temps total = démarrage + exécution

Améliorer les performances des requêtes dans PostgreSQL

EXPLAIN : Taille

 

  • Estimations de taille

 

Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)

 

 

 

  • rows : lignes à examiner pour exécuter
  • width : largeur en octets des lignes
Améliorer les performances des requêtes dans PostgreSQL

EXPLAIN avec une clause WHERE

EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep') 

Seq Scan on cheeses (cost=0.00..378.90 rows=3 width=118)
 -> Filter: (species = ANY ('{"goat","sheep"}'::text[]))
  • De bas en haut
    • Étape 1 : Filtre
    • Étape 2 : Scan séquentiel
  • Clause WHERE
    • Réduit les lignes scannées et augmente le coût total
Améliorer les performances des requêtes dans PostgreSQL

EXPLAIN avec un index

EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep') -- index on species column

Bitmap Index Scan using species_idx on cheeses (cost=0.29..12.66 rows=3 width=118)
  Index Cond: (species = ANY ('{"goat","sheep"}'::text[]))
  • Étape 1 : Bitmap Index Scan
    • Index Cond explique l'étape de scan
  • INDEX
    • Le coût de démarrage passe au-dessus de 0
    • Le coût total baisse de 379
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...