EXPLAIN + REQUETE vs EXPLAIN ANALYZE

Les utilisateurs n'aiment ni interrompre leur travail ni regarder le sablier
Répondre
Phil
Administrateur du site
Messages : 266
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

EXPLAIN + REQUETE vs EXPLAIN ANALYZE

Message par Phil »

Merci à un développeur pour sa question :
"Dans les articles de perf il y a de temps en temps des explain suivis de la requête. Pourquoi ne pas toujours utiliser explain analyze qui donne le plan tout en exécutant la requête ?"

Réponse :
Les 2 approches ne répondent pas aux mêmes besoins.
Le surcoût lié à EXPLAIN ANALYZE peut varier selon le plan d'exécution. Je préfère donc réaliser un EXPLAIN puis exécuter la requête en mesurant le temps d'exécution avec un \timing pour avoir une vision plus réaliste de sa durée.
Si le but n'est pas de déterminer l'efficacité réelle d'un ou plusieurs plans mais d'analyser en profondeur un plan donné alors EXPLAIN ANALYZE a en revanche un grand intérêt. Cette commande donne les temps d'exécution étape par étape et permet de détecter les goulets d'étranglement. Elle permet aussi pour chaque étape de comparer le nombre de lignes attendu par le planner avec la réalité.

Exemple reprenant l'environnement de test de https://pgphil.ovh/prepared_statements_10_02.php :

Code : Tout sélectionner

\timing

EXPLAIN SELECT avg(taille) FROM geants WHERE pw = 0;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=137019.11..137019.12 rows=1 width=32)
   ->  Gather  (cost=137018.89..137019.10 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=136018.89..136018.90 rows=1 width=32)
               ->  Parallel Seq Scan on geants  (cost=0.00..125613.33 rows=4162222 width=4)
                     Filter: (pw = 0)
(6 lignes)


SELECT avg(taille) FROM geants WHERE pw = 0;
         avg
----------------------
 374.9978873202780817
(1 ligne)

Durée : 1141,713 ms (00:01,142)
..
Durée : 1109,792 ms (00:01,110)
..
Durée : 1112,664 ms (00:01,113)

EXPLAIN ANALYZE SELECT avg(taille) FROM geants WHERE pw = 0;
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=137019.11..137019.12 rows=1 width=32) (actual time=8112.631..8112.632 rows=1 loops=1)
   ->  Gather  (cost=137018.89..137019.10 rows=2 width=32) (actual time=8112.537..8112.610 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=136018.89..136018.90 rows=1 width=32) (actual time=8102.997..8102.998 rows=1 loops=3)
               ->  Parallel Seq Scan on geants  (cost=0.00..125613.33 rows=4162222 width=4) (actual time=0.110..4297.982 rows=3330052 loops=3)
                     Filter: (pw = 0)
                     Rows Removed by Filter: 3281
 Planning time: 0.201 ms
 Execution time: 8129.824 ms
..
 Planning time: 0.197 ms
 Execution time: 8083.304 ms
..
 Planning time: 0.201 ms
 Execution time: 8125.264 ms


set enable_seqscan=off;
SET

EXPLAIN SELECT avg(taille) FROM geants WHERE pw = 0;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=312011.48..312011.49 rows=1 width=32)
   ->  Gather  (cost=312011.26..312011.47 rows=2 width=32)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=311011.26..311011.27 rows=1 width=32)
               ->  Parallel Index Scan using geants_i1 on geants  (cost=0.43..300605.71 rows=4162222 width=4)
                     Index Cond: (pw = 0)
(6 lignes)


SELECT avg(taille) FROM geants WHERE pw = 0;
         avg
----------------------
 374.9978873202780817
(1 ligne)

Durée : 1662,691 ms (00:01,663)
..
Durée : 1671,448 ms (00:01,671)
..
Durée : 1650,292 ms (00:01,650)

EXPLAIN ANALYZE SELECT avg(taille) FROM geants WHERE pw = 0;
                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=312011.48..312011.49 rows=1 width=32) (actual time=8727.324..8727.326 rows=1 loops=1)
   ->  Gather  (cost=312011.26..312011.47 rows=2 width=32) (actual time=8726.980..8727.288 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=311011.26..311011.27 rows=1 width=32) (actual time=8716.994..8716.995 rows=1 loops=3)
               ->  Parallel Index Scan using geants_i1 on geants  (cost=0.43..300605.71 rows=4162222 width=4) (actual time=0.154..4872.668 rows=3330052 loops=3)
                     Index Cond: (pw = 0)
 Planning time: 0.204 ms
 Execution time: 8746.290 ms
..
 Planning time: 0.203 ms
 Execution time: 8741.344 ms
..
 Planning time: 0.202 ms
 Execution time: 8808.435 ms
EXPLAIN ANALYZE pourrait laisser penser que l'efficacité du plan basé sur le PARALLEL INDEX SCAN est proche de celle du PARALLEL SEQ SCAN : 8s7 contre 8s1.
Mais, en exécution directe, la différence d'efficacité est proportionnellement bien plus importante : 1s7 avec le PARALLEL INDEX SCAN contre 1s1 avec le PARALLEL SEQ SCAN.
Cdlt. Phil - pgphil.ovh
Répondre