Re: Recherche info

Re,

Je t'ai déjà répondu en privé mais je colle aussi mes réponses ici,
histoire que d'autres puissent intervenir s'ils le souhaitent.

Le 30/09/2010 07:33, SOUCHARD Jean-Michel DSIC BIP a écrit :
> Bonjour,
> Etant en train de constituer une doc sur le fonctionnement de l'optimiseur de
> requête de PostgreSQL suite à des demandes répétées sur différents projets de
> mon administration, j'ai trouvé assez peu de choses sur le sujet. Auriez vous de
> plus amples informations (notamment explication de chaque étape du plan
> d'exécution d'une requête. Je vous livre (en ne traduisant pas volontairement
> pour le moment) ce que j'ai collecté : l'idée étant d'expliquer chaque étape
> possible (NESTED LOOP, INDEX SCAN, ...). Je joins pour info un pdf sur le
> fonctionnement de l'EXPLAIN et une URL que j'ai trouvé. (mais certains liens ne
> sont plus valides... http://wiki.postgresql.org/wiki/Using_EXPLAIN )
>

Concernant le PDF, il s'agit de la célèbre conférence de Greg Stark sur
l'EXPLAIN, mais mise à jour apparemment. Je ne connaissais pas cette
version là.

> Plan d’exécution des requêtes
>
> Un plan de requête est un ensemble de mesures sans équivoque que PostgreSQL
> utilise pour exécuter une requête. Quand une requête est exécutée, PostgreSQL
> développe un certain nombre de plans de requêtes possibles décrivant les étapes
> nécessaires pour produire le résultat souhaité. Ainsi n'importe quelle requête
> peut avoir plusieurs plans avec une formulation correcte que le planificateur de
> requêtes construit pour l'évaluation.
>
> Un coût d'exécution estimé est alors calculé en utilisant les statistiques des
> tables, des index et des colonnes recueillies par PostgreSQL. A partir de ces
> statistiques, il est en mesure d'estimer le nombre d'enregistrements et, par
> conséquent le nombre de blocs de données qui doivent être lues à partir du
> disque. Les détails tels que le potentiel dans le cache de page et le tri des
> données sont utilisées pour déterminer la vitesse à laquelle les données peuvent
> être lues. Ces vitesses sont représentés par des unités de mesure qui décrivent
> le «coût» relatif d’accès tels que des accès aléatoires, des lectures
> séquentielles sur disque e des accès à la mémoire cache. Étant donné que les
> valeurs réelles des mesures utilisées pour créer l'estimation des coûts n'ont de
> sens que les unes par rapport aux autres, et que le planificateur de requêtes ne
> cherche pas à rendre compte de la charge sur la base de données, ce coût n'est
> pas une estimation absolue de temps et ne peut être utilisé en tant que telle.
>
> Une fois que le coût des plans de requêtes a été estimé, le plus intéressant
> (celui avec le plus faible coût total) est sélectionné pour l’exécution. Bien
> que ce processus nous soit caché, la sélection du plan final ne l'est pas. La
> commande EXPLAIN va générer une description du plan de requête qui est
> finalement choisie.
>
> Pour étudier un plan de requête simple, il faut d'abord configurer pgAdmin pour
> nous fournir une analyse complète de la requête. Dans le menu Requête,
> sélectionnez le sous-menu EXPLAIN OPTIONS et assurez-vous que « Analyze » soit
> cochée. Cela permettra à la requête d’afficher à la fois les coûts prévus et le
> temps d'exécution réel.
>
> Après avoir entré votre requête dans la fenêtre de PgAdmin (comme montré
> ci-dessous), ne l’exécutez pas, mais appuyez sur le bouton EXPLAIN de la barre
> d’outil .
>

On pourrait offrir d'autres modes d'affichage comme celui du site
de despez mais je n'ai pas encore eu le temps de m'en occuper. Si tu as
des idées pour des représentations intéressantes (par exemple sur
d'autres outils que tu as pu utiliser), je suis très fortement intéressé.

> Dans le diagramme affiché, on peut voir différentes étapes utilisées pour
> l’exécution de la requête. Si vous appuyez sur chacune de ces étapes, vous
> obtenez des informations supplémentaires. Les différentes étapes possibles sont
> représentées ci-dessous :
>
> A sequence scan is a linear scan of each and every row in the table.
>
> •Fast to start up
>
> •Sequential I/O is *much *faster than random access
>
> •Only has to read each block once
>
> •Produces unordered output
>
> An index scan is just what it sounds like; PostgreSQL will scan the index of the
> indicated table, in this case *jacksonco_streets_gix*, to identify the required
> subset of records. This subset is then pulled from the table and passed to the
> next step of the query plan.
>
> The actual results tell a different story. The query planner expected only one
> row to result from the index scan, but in reality 4582 rows were retrieved.
> Comparing the cost with the actual time shows a considerable discrepancy as
> well, with 5235ms from an estimated 8.27 effort.
>
> •Random access is *much *slower than sequential I/O
>
> •Also requires additional I/O to access index
>
> •Worse, potentially has to read blocks multiple times
>
> •Only scan which produces ordered output
>
> Jointure par imbrication de boucle
> The nested loop performs a join between the two results sets using the filter
> criteria *_st_intersects(jacksonco_streets.the_geom,
> medford_citylimits.the_geom)*. The order of this step relative to the two scans
> is somewhat misleading; the nested loop uses the results of the sequence scan to
> provide the filter criteria for the index scan. The loop performs the index scan
> for each record returned by the sequence scan. The query planner has estimated
> the cost to be 9.30, which includes the execution of both the sequence and index
> scans, with an expected result of 1568 records.
>
> Much like the sequence scan, the nested loop has underestimated both the effort
> and number of results. These errors can quickly inflate the execution times of
> queries, in particular when they effect loops. It is also worth noting that the
> execution time of the nested loop overlaps both the sequence scan and index scan.
>
> •Slowest form of join in theory
>
> •But fast to produce first record
>
> •In practice it's usually desirable for OLTP queries
>
> •Performs very poorly if second child is slow
>
> •Only join capable of executing CROSS JOIN
>
> •Only join capable of inequality join conditions
>
> /A hash aggregate is used for grouping results when a hashing algorithm is
> available for the data type being grouped, in this case the varchar, *namelow*.
> It is significantly faster than the alternative of sorting and grouping it two
> stages. The query planner expects only a single row to be input into the hash
> aggregate, and as such expects only one row to be returned. We can see that the
> estimated cost starts at 13.22, after the nested loop completes, and finishes at
> 28.90, processing the 1568 records produced by the nested loop./
>

Pas grand chose à ajouter ou corriger, tout ce qui est dit me semble
exact. Il manque évidemment beaucoup de types de noeuds. Je vais essayer
de retrouver ce que j'avais commencé pour Dalibo, j'avais trouvé le
temps et les informations pour décrire certains noeuds. Je n'ai
malheureusement pas pu aller beaucoup plus loin.

Pour infos, il y a quelques infos supplémentaires dans le Korry Douglas.
Et je m'attends à en trouver bien plus dans le libre de Greg Smith.
Enfin, j'espère. Je croise les doigts violemment :)

--
Guillaume
http://www.postgresql.fr
http://dalibo.com