bd/bd06.xhtml

   1 <?xml version="1.0" encoding="utf-8" ?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
   3           "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"
   4 [
   5           <!ENTITY in  "<small style='font-size:small'>∈</small>">
   6           <!ENTITY notin  "<small style='font-size:small'>∉</small>">
   7           <!ENTITY mapsto  "↦">
   8           <!ENTITY join    "⨝">
   9 ]
  10           >
  11 <html xmlns="http://www.w3.org/1999/xhtml" >
  12   <head>
  13     <title>Prise en main de Postgresql</title>
  14
  15     <meta http-equiv="Content-Type"
  16           content="text/html; charset=utf-8" />
  17     <meta name="copyright"
  18           content="Copyright &#169; 2013 Kim Nguyễn" />
  19
  20     <!-- Load jQuery -->
  21     <script src="../jquery-2.0.3.min.js" type="text/javascript" ></script>
  22     <script src="../libs/raphael-min.js" type="text/javascript" ></script>
  23     <!-- Load the library -->
  24     <script src="../simpleWebSlides.js" type="text/javascript" ></script>
  25
  26     <link rel="stylesheet" href="../simpleWebSlides.css" type="text/css"  media="all" />
  27     <!-- Load a custom Theme, the class-element marks this style-sheet
  28          a "theme" that can be swtiched dynamicaly -->
  29     <link class="sws-theme" rel="stylesheet"  title="U-Psud style"  href="../themes/uPsud.css" type="text/css" />
  30
  31     <!-- Customize some templates and initialize -->
  32
  33     <script type="text/javascript">
  34       SWS.Config['sws-slide-change'] = SWS.Effects.slideChangeFadeOutIn;
  35       SWS.Config['sws-object-deactivate'] =  SWS.Effects.objectDeactivateFadeOut;
  36       SWS.Config['sws-object-activate'] = SWS.Effects.objectActivateFadeIn;
  37
  38       //Ensures that we load SWS at the very end, after MathJax has
  39       //been initialized
  40
  41       $(window).load(SWS.Presentation.init);
  42     </script>
  43   </head>
  44   <body>
  45     <a href="bd05.xhtml" class="sws-previous"/>
  46     <div class="sws-slide sws-cover sws-option-nofooter">
  47       <h1>Bases de données</h1>
  48       <h3>Polytech Paris-Sud</h3>
  49       <h3>Apprentis 4<sup>ème</sup> année</h3>
  50       <h1>Cours 6 : Prise en main de Postgresql</h1>
  51       <a href="mailto:kn@lri.fr">kn@lri.fr</a><br/>
  52       <a href="http://www.lri.fr/~kn/">http://www.lri.fr/~kn</a>
  53     </div>
  54
  55     <h1>Base d'exemple</h1>
  56     <div class="sws-slide">
  57       <h1>On considère la base d'exemple suivante</h1>
  58 <code>
  59  CREATE TABLE PEOPLE (<u>pid INTEGER PRIMARY key</u>,
  60                      firstname VARCHAR(30),
  61                      lastname VARCHAR(30));
  62
  63  CREATE TABLE MOVIE (<u>mid INTEGER PRIMARY KEY</u>,
  64                     title VARCHAR(90) NOT NULL,
  65                     year INTEGER NOT NULL,
  66                     runtime INTEGER NOT NULL,
  67                     rank INTEGER NOT NULL);
  68
  69  CREATE TABLE ROLE (mid INTEGER REFERENCES MOVIE,
  70                    pid INTEGER REFERENCES PEOPLE,
  71                    name VARCHAR(70),
  72                    <u>PRIMARY KEY(mid, pid, name)</u>);
  73
  74  CREATE TABLE DIRECTOR (mid INTEGER REFERENCES MOVIE,
  75                        pid INTEGER REFERENCES PEOPLE,
  76                        <u>PRIMARY KEY (mid, pid)</u>);
  77 </code>
  78     </div>
  79 <h1>EXPLAIN</h1>
  80 <div class="sws-slide">
  81 <h1>EXPLAIN ANALYSE</h1>
  82 <p>On peut demander à Postgresql d'afficher le plan qu'il calcule pour
  83   une requête avec les esitmations de coût :
  84 </p>
  85 <code>  <u>EXPLAIN</u> requête; </code>
  86 <p>Dans ce cas, la requête n'est pas évaluée. On peut aussi évaluer la
  87   requête :</p>
  88 <code>  <u>EXPLAIN ANALYSE</u> requête; </code>
  89 <p>Dans ce cas, la requête est évaluée et les coût réels sont
  90   affichés. S'ils divergent des coûts estimés, l'optimiseur s'est
  91   trompé, par exemple parce que ses statistiques ne sont pas à
  92   jour</p>
  93
  94 </div>
  95
  96 <div class="sws-slide">
  97 <h1>EXPLAIN ANALYSE (suite)</h1>
  98 <p>On considère: </p>
  99 <code>
 100   EXPLAIN ANALYSE SELECT * FROM ROLE,PEOPLE WHERE
 101                     ROLE.pid = PEOPLE.pid;
 102 </code>
 103 <p>On obtient :</p>
 104 <code> Hash Join (cost=312.07..822.95 rows=14535 width=37)
 105           (actual time=14.799..44.691 rows=14535 loops=1)
 106    Hash Cond: (role.pid = people.pid)
 107    -> Seq Scan on role (cost=0.00..238.35 rows=14535 width=20)
 108                        (actual time=0.019..7.570 rows=14535 loops=1)
 109    -> Hash (cost=175.92..175.92 rows=10892 width=17)
 110            (actual time=14.711..14.711 rows=10892 loops=1)
 111          -> Seq Scan on people (cost=0.00..175.92 rows=10892 width=17)
 112                                (actual time=0.015..5.944 rows=10892 loops=1)
 113 </code>
 114 </div>
 115 <div class="sws-slide">
 116 <h1>EXPLAIN ANALYSE (suite)</h1>
 117 <code>
 118 <u>Seq Scan</u> on people <a>(cost=0.00..175.92 rows=10892 width=17)</a>
 119                                <s>(actual time=0.015..5.944 rows=10892 loops=1)</s>
 120 </code>
 121 <ul>
 122 <li><u>Le nom de l'opérateur</u> ≡ nœud de l'arbre dans le plan de
 123   requête</li>
 124 <li><a>Estimation du coût</a> (voir suite)</li>
 125 <li><s>Coût réel</s> n'apparaît que si on a fait <tt>EXPLAIN
 126     ANALYSE</tt> (voir suite)</li>
 127 </ul>
 128 </div>
 129 <div class="sws-slide">
 130 <h1>Estimation des coût</h1>
 131 <code>
 132  (<a>cost=0.00..175.92</a> <u>rows=10892</u> <s>width=17</s>)
 133 </code>
 134 <ul>
 135 <li><a>Estimation du coût</a>. Unité : temps que met une lecture de
 136   bloc de 8ko (pour être indépendant du hardware). Le premier nombre
 137   est le temps estimé pour avoir le premier résultat. Le deuxième le
 138   temps estimé pour avoir l'ensemble.
 139 </li>
 140 <li><u>Estimation du nombre de lignes</u> dans le résultat</li>
 141 <li><s>Taille des lignes en octets</s> </li>
 142 </ul>
 143 </div>
 144 <div class="sws-slide">
 145 <h1>Coût réel</h1>
 146 <code>
 147   (<a>actual time=0.015..5.944</a> <u>rows=10892</u> <s>loops=1</s>)
 148 </code>
 149 <ul>
 150 <li><a>Coût réel</a>. Unité : <a>ms</a>. Devrait être proportionnel à
 151   l'estimation si l'optimiseur ne s'est pas trompé. Le premier nombre
 152   est le temps pour avoir le premier résultat. Le deuxième le
 153   temps pour avoir l'ensemble.
 154 </li>
 155 <li><u>Nombre de lignes</u> dans le résultat</li>
 156 <li><s>looks=x</s> l'opérateur a été appelé x fois</li>
 157 </ul>
 158 </div>
 159 <div class="sws-slide">
 160 <h1>Lecture du plan de requête </h1>
 161 <code>
 162  <u>Hash Join</u> (cost=…) (actual time=…)
 163    <u>Hash Cond</u>: (role.pid = people.pid)
 164    <u>-&gt;</u> <s>Seq Scan</s> on role (cost=…) (actual time=…)
 165    <u>-&gt;</u> <a>Hash</a> (cost=…) (actual time=…)
 166          <a>-&gt;</a> <em style="color:orange">Seq Scan</em> on people (cost=…) (actual time=…)
 167
 168 </code>
 169 <p style="text-align:center;">
 170 <img style="width:70%" src="explain_plan.svg" alt="explain" />
 171 </p>
 172 <p>Note: les projections n'apparaissent pas, on peut les voir
 173   avec <tt>EXPLAIN ANALYSE VERBOSE</tt>.</p>
 174 </div>
 175 <h1>Exemples d'opérateurs</h1>
 176 <div class="sws-slide">
 177 <h1>Nœuds fréquements rencontrés lors d'un EXPLAIN ANALYSE</h1>
 178 <p>Les opérateurs sont déclinés selon les différents algorithmes
 179   (jointure, tris, …)</p>
 180 <ul>
 181 <li><tt>Seq Scan</tt>: Scan séquentiel</li>
 182 <li><tt>Nested loop</tt>: Jointure itérative page à page</li>
 183 <li><tt>Merge sort join</tt>: Jointure par tri fusion</li>
 184 <li><tt>Hash join</tt>: jointure par hashage (généralisation de la
 185   jointure sur index)</li>
 186 <li><tt>Sort</tt>: Tri (le nœud indique l'algo de tri et la fonction
 187   de comparaison)</li>
 188 <li><tt>Index scan</tt>: scan d'un index (précise l'index et la condition)</li>
 189 <li><tt>Hash</tt>: génération d'une table de hash à la volée</li>
 190 <li><tt>Bitmap Index scan/Bitmap heap scan</tt>: génération et
 191   utilisation d'un index bitmap à la volée (voire suite)</li>
 192 <li><tt>Materialize</tt>: écriture de résultats intermédiaires sur le disque</li>
 193 </ul>
 194
 195
 196 </div>
 197 <div class="sws-slide">
 198 <h1>Retour sur les opératuers « <i>Bitmap</i> »</h1>
 199 <p>(Cours 5) si l'index est non-groupant, l'utilisation de l'index
 200   peut provoquer une séquence de chargement/déchargement de pages
 201   ruinant les performances
 202 </p>
 203 <p>Solution en deux phases</p>
 204 <ul>
 205   <li>Scanner l'index et créer un bitmap de taille N où N est le
 206   nombre de pages de la relation. Pour chaque entrée d'index
 207   satisfaisant le résultat, mettre le bit correspondant à 1 (phase
 208   Bitmap Index Scan)
 209   </li>
 210   <li>
 211     Parcourir la relation dans l'ordre du disque, page à page. Si la
 212     page est à 1 dans le bitmap, on la charge, sinon on l'ignore.
 213     Une fois la page chargée il faut <u>réévaluer la condition</u> car on
 214     a oublié quels étaients les résultats de l'index (phase Bitmap
 215     Heap Scan)
 216   </li>
 217 </ul>
 218 <p>Intéret: Un bitmap est petit (si la relation contient 10000 pages,
 219   le bitmap contient 10000 bits ou environs 1250 octets (soit moins
 220   d'une page).</p>
 221 <p>Cela permet aussi de répondre efficacement aux requêtes
 222   booléenes complexes (i.e. autre qu <tt>AND</tt>). En effet on
 223   calcule un bitmap pour chaque sous-condition, et on fait les
 224   opérations entre bitmap bit à bit.
 225 </p>
 226 </div>
 227 <h1>Démo</h1>
 228 </body>
 229 </html>