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

Pour obtenir le plan de l’exécution d’une requête :
EXPLAIN [ANALYZE] requ^
ete
Plan d’exécution de requêtes
Wieslaw Zielonka
23 novembre 2013
scan d’une table
BEGIN;
EXPLAIN ANALYZE ... ;
ROLLBACK;
Scan séquentiel - sequential scan
L’opération de base c’est le scan (parcours) d’une table.
Dans les exemples j’utilise la table suivante :
create table test ( a int ,
c r e a t e i n d e x i n d a on t e s t
c r e a t e i n d e x i n d b on t e s t
insert into test
values (1 ,1 ,1) , (2 ,2 ,2) ,
(5 ,5 ,5);
EXPLAIN seul : juste estimation de temps, la requête ne sera pas
exécutée.
Si EXPLAIN ANALYZE alors la requête sera exécutée et on obtient
le temps réel d’exécution. Mais l’exécution est peut-être indésirable
pour INSERT DELETE UPDATE.
Pour annuler les effets d’une telle requête faire :
b int , c in t ) ;
using btree (a );
u s i n g ha s h ( b ) ;
(3 ,3 ,3) , (4 ,4 ,4) ,
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 w i d t h
( a c t u a l t i m e = 0 . 0 3 8 . . 0 . 0 4 9 rows=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 1 5 ms
(3 rows )
Sequential scan
Indexe n’est pas utilisé. On parcourt tous les enregistrements de la
table bloc par bloc.
Exemple. La table T possède l’attribut X . On exécute la requête
select * from T where X = 4;
La table possède 100 enregistrements rangé dans 5 blocs (dans 5
pages mémoire), 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és sur le
disque. Donc on lit séquentiellement tous les blocs qui contiennent
T.
Index Scan
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 i d
R echeck 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 i d t h
−> 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 i d t h
I n d e x Cond : ( a = 4)
−> Bitmap I n d e x Scan on i n d b
( c o s t = 0 . 0 0 . . 4 . 3 3 rows =10 w i d t h
I n d e x Cond : ( b = 3)
T o t a l r u n t i m e : 0 . 1 7 8 ms
(9 rows )
Bitmap Index Scan versus 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 :
explain analyze sel ect ∗
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 w i d t h =12)
( a c t u a l t i m e = 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 0 7 ms
(3 rows )
Quelle différence entre Bitmap Index Scan et Index Scan ?
Les deux types de parcours utilise l’index mais les deux types de
scan cherchent les données sur le disque dur aux moment différent.
Bitmap Index Scan versus Index Scan
Supposons qu’il existe un indexe B-arbre sur l’attribut X , on
exécute une requête 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
Donc les enregistrements recherchés se trouvent dans les bloc 4, 2,
4 et 2 en suivant l’ordre de pointeurs sur les feuilles de B-arbre.
enregistrements avec X=2
Index scan
On trouve les enregistrements grâce à l’index. Des qu’on trouve
dans une feuille de B-arbre un pointeur vers un enregistrement
avec X = 2 on cherche immédiatement 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émoire principale pour les données
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és (mais juste les pointeurs pas les données
pour l’instant).
On trie les pointeurs trouvés par l’ordre physique de blocs. C’est la
phase Bitmap Index Scan.
Et finalement, on lit chaque bloc concerné et on parcourt tous les
enregistrements recherchés présents dans ce bloc (l’étape 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émoire cela donne deux lectures de
blocs.
Efficacité de différents scan
Et d’autres opérations ?
EXPLAIN ANALYZE s e l e c t R . b , S . b
from t e s t a s R , t e s t a s S
where R . a=S . c ;
Si la conditions de recherche est peu sélective (le nombre
d’enregistrements qui satisfont la condition est une fraction
significative de la table) alors le scan séquentiel le plus efficace.
Si très peu d’enregistrements satisfont la condition de recherche
alors le Bitmap Index Scan le plus efficace.
Si le nombre d’enregistrement recherché n’est ni trop grand ni trop
petit alors Index Scan est le plus efficace.
Et d’autres opérations ? (cont)
Dans l’exemple sur le transparent précédent plusieurs opérations
formant un arbre d’exécution :
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
Merge J o i n
( c o s t = 1 0 0 0 0 0 0 0 1 3 5 . 3 4 . . 1 0 0 0 0 0 0 0 4 9 9 . 8 1 ro ws
( a c t u a l t i m e = 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 ro ws =1940 w i d t h =8)
( a c t u a l t i m e = 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 3 5 . 3 4 . . 1 0 0 0 0 0 0 0 1 4 0 . 1 9 ro ws
( a c t u a l t i m e = 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 r t 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 0 0 . 0 0 . . 1 0 0 0 0 0 0 0 0 2 9 . 4 0 ro ws
( a c t u a l t i m e = 0 . 0 0 8 . . 0 . 0 2 1 ro ws=5 l o o p s =1)
Classification d’algorithmes
On distingue trois classes d’algorithmes :
◮
scan séquentiel,
◮
basés sur le tri
◮
le résultat (table) trié dans la mémoire avec le tri rapide,
◮
basés sur le hachage
◮
index scan utilisant l’index inda,
◮
basés sur les indexes.
◮
et finalement la jointure : merge join.
Temps d’exécution
Le temps d’exécution d’une requête dépend essentiellement de
nombre des opérations read et write sur les disque dur. Ce
nombre dépend de l’algorithme utilisé mais aussi de la manière de
stocker une relation.
On suppose ici que les relations sont stockées de façon groupée
(clustered), c’est-à-dire
◮
un bloc de mémoire contient uniquement les enregistrements
qui proviennent d’une table,
◮
chaque table utilise le minimum de blocs de mémoire
nécessaires, les enregistrements d’une table remplissent
complètement chaque bloc sauf peut-être le dernier.
Classification des algorithmes par le nombre d’opération de
lecture/écriture
Les algorithmes peuvent être divisés 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é, écrit sur le disque et plus tard lu pour la deuxième
fois pour être traité.
◮
les algorithmes avec plusieurs passage – le lecture/écriture de
chaque bloc s’effectue plusieurs fois.
Dans la plupart de cas on lit et écrit les relations par blocs de
mémoire.
Le système maintient un ensemble de M tampon dans la mémoire
principale, chaque tampon de taille d’un bloc.
Je note B(R) le nombre de blocs utilisés pour stocker la relation R.
Algorithmes avec un passage
Union ensembliste R UNION S
Supposons que B(R) ≤ B(S) et que B(R) ≤ M − 1.
(1) Lire R dans M − 1 tampons, construire une structure de
recherche sur les enregistrements de R qui sont dans la
mémoire (table de hachage ou arbre équilibré) en utilisant tout
enregistrement comme clé de recherche.
(2) Envoyer sur la sortie tous les blocs de R.
(3) Lire un bloc de S dans le tampon M. Pour chaque
enregistrement t de S chercher t dans R et si t non trouvé
alors envoyer t vers la sortie, sinon écarter t.
(4) Répéter (3) tant qu’il y a de blocs de S.
Algorithmes avec un passage
Algorithmes avec un passage
Différence ensembliste Supposons toujours que B(R) ≤ B(S) et
que B(R) ≤ M − 1.
Il faut distinguer entre R EXCEPT S et S EXCEPT R.
(1) Dans les deux cas lire R dans M − 1 tampons, construire une
structure de recherche sur les enregistrements de R qui sont
dans la mémoire (table de hachage ou arbre équilibré) en
utilisant tout enregistrement comme clé de recherche.
(2) Pour R − S :
◮
◮
◮
Intersection ensembliste R INTERSECT S
Exercice.
lire chaque bloc de S dans le tampon M,
pour chaque enregistrement t de S, si t dans R alors supprimer
t dans la copie de R qui est dans la mémoire,
après avoir examiner tous les enregistrements de S copier les
enregistrements de R qui restent dans la mémoire sur la sortie.
(3) pour S − R :
◮
lire chaque bloc de S dans le tampon M, et pour chaque
enregistrement t de S qui ne se trouve pas dans R copier t sur
la sortie.
Jointure naturelle avec un passage
Nested-loop join
Deux relations R(X , Y ) et S(Y , Z ). Jointure naturelle R ⋊
⋉S
(d’autre type de jointures implémentées de manière similaire).
L’algorithme peut être utilisé si pour une relation, par exemple R,
B(R) < M.
(1) lire R dans M − 1 tampons, construire une structure de
recherche sur les enregistrements de R qui sont dans la
mémoire (table de hachage ou arbre équilibré) en utilisant les
attributs Y comme clé de recherche.
(2) lire chaque bloc de S dans le tampon M et pour chaque t dans
S trouver tous les enregistrements de R avec la même clé que
la clé de t en utilisant la structure de recherche. Pour chaque
t ′ trouvé faire la jointure avec t et envoyer vers la sortie.
Le nombre des opérations i/o de l’ordre B(R) + B(S).
Possible à effectuer sur les tables de taille quelconque.
R(X , Y ) ⋊
⋉ S(Y , Z )
On suppose que B(S) ≤ B(R) mais B(S) peut être plus grand que
M (les tables n’entrent pas totalement dans la mémoire principale).
Nested-loop join – algorithme
Nested-loop join – complexité
POUR chaque tranche de M-1 blocs de S FAIRE
lire cette tranche dans la mémoire principale
construire une structure de recherche sur
les enregistrement de S
présents dans la mémoire avec la clé de recherche Y
POUR chaque bloc b de R FAIRE
lire b dans la mémoire principale
POUR chaque enregistrement t de b FAIRE
trouver les enregistrements s de S
dans la mémoire
qui font la jointure avec t
La boucle externe itère B(S)/(M − 1) fois. Et pour chaque
itération externe on lit M − 1 blocs de S et B(R) blocs de R.
Donc au total
B(S)
B(S)B(R)
(M − 1 + B(R)) = B(S) +
M −1
M −1
sortir la jointure de t avec chaque s trouvé
FIN POUR
FIN POUR
Algorithmes à deux passage basés sur le tri
Les algorithmes basés sur le tri possèdent le même ingrédient — le
tri de sous listes :
Dans la plupart de cas deux passages sont suffisants même pour de
très grandes tables. Et si on comprend les algos à deux passages on
peut soi-même concevoir des algos à plusieurs passages.
Pour cette raison on considère uniquement les algos à deux
passages.
(1) Lire M blocs de la table R dans la mémoire principale,
(2) faire le tri des enregistrements qui sont dans la mémoire en
utilisant un algorithme de tri interne (par exemple quicksort),
(3) écrire les M blocs sur le disque.
Donc à la fin de cet algorithme chaque tranche de M blocs est
triée, on l’appelle sous liste.
Élimination de doublons avec un tri
Trier les enregistrements de R en sous listes.
Mettre dans chaque tampon un bloc de chaque sous liste (au
début le premier) et maintenir un pointeur par liste. Mettre sur la
sortie le plus petit élément pointé en ignorant les enregistrements
qui lui sont identiques.
La complexité : B(R) pour lire chaque bloc pour le tri, B(R) pour
sauvegarder, B(R) pour lire encore une fois chaque bloc. Au total
3B(R).
Jointure basée sur le tri - merge join
1. Trier R avec le tri par fusion externe, faire pareil pour S.
2. fusionner R et S en utilisant deux tampons : un pour le bloc
courant de R et l’autre pour le bloc courant de S. Soit R.y et
S.y le valeurs courantes de y dans les deux tampons.
◮
◮
◮
si R.y < S.y avancer le pointeur courant dans R,
si S.y < R.y avancer le pointeur courant dans S,
si R.y = S.y = a faire la jointure de tous les enregistrements
avec la valeur a de Y et avancer les pointeurs courants dans R
et S jusqu’à la première valeur supérieure à a. Si nécessaire lire
les blocs suivants de R et S.
Jointure basée sur le tri
Il existe une variante plus efficace de jointure basée sur un tri.
Dans cette version on trie uniquement chaque sous liste de R et de
S (pas de tri global).
Supposons que le nombre de sous listes (dans R et dans S) est
inférieure à M. Dans ce cas on avance dans toutes ces listes en
même temps en mettant dans les tampons les blocs courants de
chaque liste.
Cette version a complexité 3(B(R) + B(S)).
Algorithmes basés sur le hachage
L’idée de base : si les tables trop grandes pour entrer dans la
mémoire principale alors faire hachage en utilisant la clé de
hachage appropriée.
Les enregistrements qui doivent être examinés ensemble pour
effectuer une opération auront la même valeur de hachage.
Fonction de hachage :
h : cle → valeur de hachage
Grouper les enregistrements avec la même clé de hachage
L’algorithme de base c’est l’algorithme qui permet de regrouper les
enregistrements selon leurs valeurs de hachage.
On suppose que SGBD possède M tampons et les valeurs de
hachage possible sont {1, 2, . . . , M − 1}.
Les premiers M − 1 tampons sont utilisés pour classer les
enregistrements selon leurs valeurs de hachage, le tampon M
contient le bloc courant.
Donc l’opération peut être effectuée en examinant chaque fois les
groupes d’enregistrements avec la même valeur de hachage.
Grouper les enregistrements avec la même clé de hachage
Initialiser les tampons 1,...,M-1 vides
POUR chaque bloc b de la table R FAIRE
lire b dans le tampon M
POUR chaque enregistrement t dans b FAIRE
SI le tampon h(t) n’est pas plein ALORS
ajouter t dans le tampon h(t)
SINON BEGIN
sauvegarder le tampon h(t) sur le disque
vider le tampon h(t)
copier t dans le tampon h(t)
END
END POUR
END POUR
POUR chaque tampon i, 1<=i<M FAIRE
SI le tampon i non vide ALORS
le sauvegarder sur le disque
END POUR
Algorithme Hash-Join
R(X , Y ) ⋊
⋉ S(Y , Z )
La clé de hachage calculée sur Y . Donc les enregistrements avec la
même valeur de hachage ont la même valeur de Y .
Après l’étape de hachage de R et S pour chaque valeur de hachage
il suffit de faire la jointure comme dans l’algorithme nested-loop
join.
Exemple
Soit b(R) = 1000, b(S) = 500 et SGBD dispose de M = 101
tampons.
Donc on choisit la fonction de hachage avec 100 valeurs :
1, . . . , 100.
Après avoir haché R et S on obtient en moyenne 10 blocs de R et
5 blocs de S avec la même valeur de hachage. En particulier
5 < 100 donc pour chaque valeur de hachage i , le 5 bloc de S avec
la valeur de hachage i entrent dans les tampons. Le 6ème tampon
sera utilisé pour scanner les blocs de R avec la même valeur de
hachage i .
Il y aura 1000 + 500 read et 1500 write pour effectuer le hachage
sur R et S. Et ensuite encore une fois 1500 read pour la jointure
(on compte pas la sauvegarde de résultat).
Comment aider le planificateur à bien choisir le plan
d’exécution ?
1. Exécuter de temps en temps ANALYZE table(col1,...).
ANALYZE trouve les données statistiques sur une table, ces
données sont utilisées par le planificateur de requêtes.
2. Préférer les jointures plutôt que des sous requêtes.
3. Éviter surtout la sous requête IN (SELECT ...).
algorithmes basés sur les indexes
Indexes permettent d’améliorer la vitesse de la sélection Si les
critères de sélections sont restrictifs alors le tables obtenues sont
petites et les jointures prennent moins de temps. Par contre juste
pour faire une jointure les indexes ne sont pas très utiles.