Cycle de vie d'une requête et planificateur

Améliorer la performance des requêtes dans PostgreSQL

Amy McCarty

Instructor

Cycle de vie de base d'une requête

Système Étapes frontales Processus dorsaux
1 Analyseur Envoie la requête à la base Vérifie la syntaxe. Traduit le SQL en une syntaxe plus proche de la machine selon des règles internes.
2 Planificateur et Optimiseur Évalue et optimise les tâches S'appuie sur des statistiques pour créer le plan. Calcule les coûts et choisit le meilleur plan.
3 Exécuteur Retourne les résultats Suit le plan pour exécuter la requête.
Améliorer la performance 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 de pg_tables
    • Optimisation basée sur le temps
1 Plan tree: https://www.postgresql.org/docs/current/querytree.html
Améliorer la performance des requêtes dans PostgreSQL

Statistiques de pg_tables

SELECT * FROM pg_class
WHERE relname = 'mytable'

-- échantillon de colonnes de sortie
| relname | relhasindex |

SELECT * FROM pg_stats
WHERE tablename = 'mytable'

-- échantillon de colonnes de sortie
null_frac | avg_width | n_distinct |
  • Index de colonnes
  • Nombre de valeurs nulles
  • Largeur des colonnes
  • Valeurs distinctes
Améliorer la performance des requêtes dans PostgreSQL

EXPLAIN

 

  • Fenêtre 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 la performance des requêtes dans PostgreSQL

EXPLAIN : Scan

 

  • Étape du plan de requête
  • Retourne des lignes

 

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

 

 

 

 

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

EXPLAIN : Coût

 

  • Sans dimension
  • Comparer des structures au même résultat
    • 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 la performance 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 la performance 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 : filtrage
    • Étape 2 : parcours séquentiel
  • Clause WHERE
    • Réduit les lignes à parcourir et augmente le coût total
Améliorer la performance 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 balayage
  • INDEX
    • Le coût de démarrage augmente à partir de 0
    • Le coût global baisse de 379
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...