Re: Recherche info

Le 07/10/2010 00:09, Guillaume Lelarge a écrit :
> 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 :)
>
>

Et tant que j'y suis avant d'aller me coucher,
http://encodestatistics.org/publications/statistics_and_postgres.pdf

Très bon PDF sur le planificateur. Mais peut-être le connais-tu déjà.

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