Plan d`exécution de requêtes scan d`une table Scan séquentiel

Plan d’ex´ecution de requˆetes
Wies law Zielonka
23 novembre 2013
Pour obtenir le plan de l’ex´ecution d’une requˆete :
EXPLAIN [ANALYZE] requ^ete
EXPLAIN seul : juste estimation de temps, la requˆete ne sera pas
ex´ecut´ee.
Si EXPLAIN ANALYZE alors la requˆete sera ex´ecut´ee et on obtient
le temps r´eel d’ex´ecution. Mais l’ex´ecution est peut-ˆetre ind´esirable
pour INSERT DELETE UPDATE.
Pour annuler les effets d’une telle requˆete faire :
BEGIN;
EXPLAIN ANALYZE ... ;
ROLLBACK;
scan d’une table
L’op´eration de base c’est le scan (parcours) d’une table.
Dans les exemples j’utilise la table suivante :
c r e a t e t a b l e t e s t ( a i nt , b i nt , c i n t ) ;
c r e a t e i n d e x i n d a on t e s t u s i n g b t r e e ( a ) ;
c r e a t e i n d e x i n db on t e s t u s i n g h as h ( b ) ;
i n s e r t i n t o t e s t
v a l u e s ( 1 , 1 , 1 ) , ( 2 , 2 , 2 ) , ( 3 , 3 , 3 ) , ( 4 , 4 , 4 ) ,
( 5 , 5 , 5 ) ;
Scan s´equentiel - sequential scan
e x p l a i n a n a l y z e s e l e c t ∗from t e s t where a <> 4 ;
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Seq Scan on t e s t ( c o s t = 0 . 0 0 . . 3 4 . 2 5 rows =1930 wi dth
( a c t u a l ti me = 0 .0 3 8 . .0 . 0 4 9 row s=4 l o o p
F i l t e r : ( a <> 4)
T o t a l r u n t i m e : 0 . 1 15 ms
(3 rows )

Sequential scan
Indexe n’est pas utilis´e. On parcourt tous les enregistrements de la
table bloc par bloc.
Exemple. La table Tposs`ede l’attribut X. On ex´ecute la requˆete
select * from T where X = 4;
La table poss`ede 100 enregistrements rang´e dans 5 blocs (dans 5
pages m´emoire), chaque bloc contient 20 enregistrements. Il y a 4
enregistrements qui satisfont la condition X= 4, 2 dans le bloc 2
et 2 dans le bloc 4.
Sequential scan lira tous les blocs un par un pour retrouver les 4
enregistrements dans l’ordre dans lequel les blocs sont rang´es sur le
disque. Donc on lit s´equentiellement tous les blocs qui contiennent
T.
Bitmap Index Scan
e x p l a i n a n a l y z e s e l e c t ∗from t e s t
where a = 4 OR b=3 AND c =3;
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Bitmap Heap Scan on t e s t
( c o s t = 8 . 6 6 . . 1 8 . 9 9 rows =10 w idt
Rech eck Cond : ( ( a = 4) OR ( b = 3 ) )
F i l t e r : ( ( a = 4) OR ( ( b = 3) AND ( c = 3 ) ) )
−>BitmapOr
( c o s t = 8 . 6 6 . . 8 . 6 6 rows =19 w id th
−>Bitmap I n d e x Scan on i n d a
( c o s t = 0 . 0 0 . . 4 . 3 3 rows =10 w id th
I n d e x Cond : ( a = 4)
−>Bitmap I n d e x Scan on i n db
( c o s t = 0 . 0 0 . . 4 . 3 3 rows =10 w id th
I n d e x Cond : ( b = 3)
T o t a l r u n t i m e : 0 . 1 78 ms
(9 rows )
Index Scan
Dans l’exemple suivant Postgres a choisi d’utiliser Bitmap Index
Scan. La directive suivante invalide l’utilisation de Bitmap Index
Scan.
set enable_bitmapscan=off;
Et dans ce cas Posgres utilise Index Scan :
e x p l a i n a n a l y z e s e l e c t ∗
from t e s t where a >2 ;
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
I n d e x Scan u s i n g i n d a on t e s t
( c o s t = 0 . 0 0 . . 5 5 . 5 7 rows =647 wi dt h =12)
( a c t u a l ti me = 0 .0 3 8 . .0 . 0 5 0 rows=3 l o o p s =1)
I n d e x Cond : ( a >2)
T o t a l r u n t i m e : 0 . 1 07 ms
(3 rows )
Bitmap Index Scan versus Index scan
Quelle diff´erence entre Bitmap Index Scan et Index Scan ?
Les deux types de parcours utilise l’index mais les deux types de
scan cherchent les donn´ees sur le disque dur aux moment diff´erent.

Bitmap Index Scan versus Index Scan
Supposons qu’il existe un indexe B-arbre sur l’attribut X, on
ex´ecute une requˆete avec la condition X= 2 et les feuilles de ce
B-arbre pointent sur les 4 enregistrements avec X= 2 comme
ci-dessous :
index B-arbre
enregistrements avec X=2
Donc les enregistrements recherch´es se trouvent dans les bloc 4, 2,
4 et 2 en suivant l’ordre de pointeurs sur les feuilles de B-arbre.
Index scan
On trouve les enregistrements grˆace `a l’index. Des qu’on trouve
dans une feuille de B-arbre un pointeur vers un enregistrement
avec X= 2 on cherche imm´ediatement ce enregistrements sur le
disque (on lisant tout le bloc qui le contient).
Dans l’exemple, on lit les blocs 4,2,4,2 dans cet ordre. Notez que
s’il y a un seul tampon dans la m´emoire principale pour les donn´ees
alors il faudra 4 lectures de blocs pour lire les 4 enregistrements.
Bitmap Index scan
On utilisant l’index on cherche les pointeurs vers les
enregistrements concern´es (mais juste les pointeurs pas les donn´ees
pour l’instant).
On trie les pointeurs trouv´es par l’ordre physique de blocs. C’est la
phase Bitmap Index Scan.
Et finalement, on lit chaque bloc concern´e et on parcourt tous les
enregistrements recherch´es pr´esents dans ce bloc (l’´etape Bitmap
Heap Scan).
Dans notre exemple : on lit le bloc 4 et deux enregistrements qu’il
contient, ensuite bloc 2 et deux enregistrements dans ce bloc.
Avec un seul tampon dans la m´emoire cela donne deux lectures de
blocs.

Efficacit´e de diff´erents scan
Si la conditions de recherche est peu s´elective (le nombre
d’enregistrements qui satisfont la condition est une fraction
significative de la table) alors le scan s´equentiel le plus efficace.
Si tr`es peu d’enregistrements satisfont la condition de recherche
alors le Bitmap Index Scan le plus efficace.
Si le nombre d’enregistrement recherch´e n’est ni trop grand ni trop
petit alors Index Scan est le plus efficace.
Et d’autres op´erations ?
EXPLAIN ANALYZE s e l e c t R . b , S . b
from t e s t as R , t e s t a s S
where R . a=S . c ;
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Merge J o i n
( c o s t = 1 0 0 0 0 0 0 0 1 35 . 3 4. .1 0 0 0 00 0 0 49 9 . 8 1 r ows
( a c t u a l ti me = 0 . 1 2 0 . . 0 . 1 7 8 ro ws=5 l o o p s =1)
Merge Cond : ( r . a = s . c )
−>I n d e x Scan u s i n g i n d a on t e s t r
( c o s t = 0 . 0 0 . . 7 7 .3 5 rows =1940 w id t h =8)
( a c t u a l ti me = 0 . 0 1 0 . . 0 . 0 2 4 ro ws=5 l o o p s =1)
−>S o r t
( c o s t = 1 0 0 0 0 0 0 0 1 35 . 3 4. .1 0 0 0 00 0 0 14 0 . 1 9 r ows
( a c t u a l ti me = 0 . 0 8 8 . . 0 . 0 9 7 ro ws=5 l o o p s =1)
S o r t Key : s . c
S o rt Method : q u i c k s o r t Memory : 17kB
−>Seq Scan on t e s t s
( c o s t = 1 0 0 0 0 0 0 0 0 00 . 0 0. .1 0 0 0 00 0 0 02 9 . 4 0 r ows
( a c t u a l ti me = 0 . 0 0 8 . . 0 . 0 2 1 ro ws=5 l o o p s =1)
Et d’autres op´erations ? (cont)
Dans l’exemple sur le transparent pr´ec´edent plusieurs op´erations
formant un arbre d’ex´ecution :
◮scan s´equentiel,
◮le r´esultat (table) tri´e dans la m´emoire avec le tri rapide,
◮index scan utilisant l’index inda,
◮et finalement la jointure : merge join.
Classification d’algorithmes
On distingue trois classes d’algorithmes :
◮bas´es sur le tri
◮bas´es sur le hachage
◮bas´es sur les indexes.

Temps d’ex´ecution
Le temps d’ex´ecution d’une requˆete d´epend essentiellement de
nombre des op´erations read et write sur les disque dur. Ce
nombre d´epend de l’algorithme utilis´e mais aussi de la mani`ere de
stocker une relation.
On suppose ici que les relations sont stock´ees de fa¸con group´ee
(clustered), c’est-`a-dire
◮un bloc de m´emoire contient uniquement les enregistrements
qui proviennent d’une table,
◮chaque table utilise le minimum de blocs de m´emoire
n´ecessaires, les enregistrements d’une table remplissent
compl`etement chaque bloc sauf peut-ˆetre le dernier.
Dans la plupart de cas on lit et ´ecrit les relations par blocs de
m´emoire.
Le syst`eme maintient un ensemble de Mtampon dans la m´emoire
principale, chaque tampon de taille d’un bloc.
Je note B(R) le nombre de blocs utilis´es pour stocker la relation R.
Classification des algorithmes par le nombre d’op´eration de
lecture/´ecriture
Les algorithmes peuvent ˆetre divis´es en classes en fonctions de
nombre de lecture sur le disque dur :
◮les algorithmes d’une passage – chaque bloc est lu une seule
fois,
◮les algorithmes avec deux passage – chaque bloc est lu une
fois, trait´e, ´ecrit sur le disque et plus tard lu pour la deuxi`eme
fois pour ˆetre trait´e.
◮les algorithmes avec plusieurs passage – le lecture/´ecriture de
chaque bloc s’effectue plusieurs fois.
Algorithmes avec un passage
Union ensembliste RUNION S
Supposons que B(R)≤B(S) et que B(R)≤M−1.
(1) Lire Rdans M−1 tampons, construire une structure de
recherche sur les enregistrements de Rqui sont dans la
m´emoire (table de hachage ou arbre ´equilibr´e) en utilisant tout
enregistrement comme cl´e de recherche.
(2) Envoyer sur la sortie tous les blocs de R.
(3) Lire un bloc de Sdans le tampon M. Pour chaque
enregistrement tde Schercher tdans Ret si tnon trouv´e
alors envoyer tvers la sortie, sinon ´ecarter t.
(4) R´ep´eter (3) tant qu’il y a de blocs de S.