Forums PostgreSQL.fr

smougey

Membre

Tri physique des données

Bonjour

Savez vous s'il est possible de définir sur pg une clé de tri physique des données sur le disque ?

Mon problème est que je suis limité par les temps d'accès aux disques (ce sont des 10K/mn SAS), et que je connais l'ordre exact de mes requêtes, toujours reproductible. Je souhaiterais donc que mes données soient triées selon cet ordre, afin de réduire l'impact des temps d'accès.

Merci

gleu

Administrateur

Re : Tri physique des données

La commande CLUSTER permet de trier les données d'une table suivant l'ordre d'un index. En espérant que j'ai bien compris votre question...

---

Guillaume.

smougey

Membre

Re : Tri physique des données

Merci, c'était exactement ce que je cherchais !!!

kris_le_parisien

Membre

Re : Tri physique des données

Bonjour,

J'aurais une question sur les index cluster et index non cluster.

Tout d'abord, pourriez-vous me confirmez si j'ai bien compris le fonctionnement
1) Pour les deux types d'index, les feuilles de l'index (si organisé en B-tree) pointent vers les pages du fichier de données.
2) Dans une feuille on a comme information, la valeur de clé (ayant servi pour la recherche), l'identifiant de la page dans le fichier de données, et le deplacement dans la page pour trouver l'enregistrement. Question: il y a -t-il autant de feuilles que de valeurs de clés différentes?

Voici comment j'ai compris la question des acces disques.
3)
Avec un index on retrouve l'identifiant d'une page où est stocké l'enregistrement recherché. Ensuite il faut copier la page du disque dans le cache. Cela coute un acces disque. Puis on recherche l'enregistrement dans la page, et on le renvoie.

Si plusieurs enregistrements correspondent à la recherche,
4) - dans le cas d'un cluster les enregistrements  sont triés. 2 enregistrement peuvent donc se retrouver dans une même page (si elle est assez grande pour tous les contenir), il y a donc beaucoup de chances que l'on ait moins d'acces disques (puisque l'on copie moins de pages).
5) - dans le cas d'un index non-clusterisé: les enregistrement du fichier de données ne sont pas triées , donc il est possible que les resultats correspondant à la clé de recherche X=640 soient dans des pages du fichier de données très différentes.

J'ai crée 2 tables test_cluster(colid integer) et test_idx(colid integer) remplis de la même manière.  La fonction ayant servi à remplir ces tables est présentée plus bas.
Je crée deux index btree. Un index cluster idx_cluster   sur la table test_cluster et un index idx_num sur test_idx.

Je lance alors la même requête sur les deux tables:
un select avec condition d'égalité.
select * from nom_table where colid =100 000.  Etant donnee que chaque table contient 5 occurences de la valeur 640. Le order by random() est censé les répartir aléatoirement sur la table.

Je regarde dans pg_statio_all_indexes pour connaitre le nombre d'acces disque pour chacun.
Voici ce que je trouve:
6) - pour  idx_num : idx_blks_read = 2, idx_blks_hit = 1 -->   ca veut dire que pour trouver des resultats on a du copier deux pages disques dans le cache (on y a trouvé 4 occurences), et pour une des occurences il a suffit d'aller la chercher dans le cache associé à l'index (je sens que c'est faux, corrigez-moi s'il vous plait)
7) - pour  idx_cluster: même résultat!Or vu que les données sont triées, je pensais qu'il suffisait de copier une page du disque dans le cache, puis de retrouver tous les enregistrements dans ce cache.

Donc voila, quand il s'agit d'une recherche d'égalité, je trouve toujours les mêmes résultats pour les deux types d'index.
Quand il s'agit par contre d'une requete portant sur un ensemble de valeurs de clés alors là il y a moins d'acces au disque avec l'index clusterisé qu'avec celui non clusterisé. Je suppose que c'est là que ce que j'ai dit sur le tri s'applique.

7) Enfin, apres avoir executé une fois l'instruction, le cache de idx_num contient quatre tampons (j'ai installé pg_buffercache). Problème même si je me deconnecte  puis me reconnecte, les tampons sont toujours present dans le cache. Je ne sais comment vider le cache.

Précision: shared buffer= 32 Mo

Bref,  j'espère que vous pourrez m'aider à comprendre les points que j'ai mal compris. (Je crois connaitre le fonctionnement general, mais il manque le détail du deroulement)

8) (Pour l'exemple des caches, j'ai aussi refait l'exemple du site http://www.dalibo.org/glmf107_gestion_m … postgresql, et j'ai voulu inserer 1000 entiers dans une table.
Insertion 1 ere ligne:   le fichier de données contient un bloc de 8Ko et on a un tampon dans le cache  --> Normal.
Puis jusqu' à l'insertion de la 227 ligne, on est toujours dans la même configuration. Apres 228 lignes inserées, j'ai alors 4 tampons dans le cache (je ne sais pas pourquoi cela arrive d'un coup, il y a t-il un lien avec le cache du noyau de l'OS?.   ) Et le fichier de données contient 16Ko (ça ne correspond pas à 4 tampons de 8Ko, est -ce normal?)

En vous remerciant.

Dernière modification par kris_le_parisien (02/05/2011 09:26:56)

kris_le_parisien

Membre

Re : Tri physique des données

La fonction de remplissage des tables:

declare

ligne record;

begin
for j in 1..5 loop
for ligne in execute 'select x from generate_series(1, 100000) as x order by random()' loop
  insert into nom_table values(ligne.x);
end loop;

end loop;
return 1;
end;

Marc Cousin

Membre

Re : Tri physique des données

Dans l'ordre des questions:
- Il n'y a pas un type d'index différent pour CLUSTER. C'est toujours un b-tree. C'est juste que la table est triée physiquement par rapport à cet index
- Il y a évidemment plusieurs clés par page feuille du btree. Les pages feuilles sont d'ailleurs chaînées entre elles.

Pour ce qui est du cluster, vous avez compris. À un détail près: même si les données ne tiennent pas sur une seule page, ça marche: le système d'exploitation lit (presque) toujours plusieurs pages consécutives d'un fichier quand on lui demande une page. C'est le mécanisme de readahead.

Ensuite, pour les statistiques collectées, c'est normal: les index eux restent triés par rapport à leur clé, c'est le principe du btree. C'est pour cela que vous avez les mêmes statistiques de lecture sur l'index. C'est sur la table (heap) que les accès seront différents.

---

Marc.

kris_le_parisien

Membre

Re : Tri physique des données

Bonjour,
merci de votre reponse,

Il y a évidemment plusieurs clés par page feuille du btree. Les pages feuilles sont d'ailleurs chaînées entre elles

Pour ce qui est du cluster, vous avez compris. À un détail près: même si les données ne tiennent pas sur une seule page, ça marche: le système d'exploitation lit (presque) toujours plusieurs pages consécutives d'un fichier quand on lui demande une page. C'est le mécanisme de readahead..

A)  Je me rend compte que je parlais de pages et non de blocs.  On a bien: une feuille de l'index pointe vers une page (et pas un bloc) du fichier de données. Pour lire une page, il faut lire les blocs qui la contienne. Est ce que dans le cas d'un select, quand on veut lire une page,
on recherche alors dans cette page le premier bloc qui satisfait la valeur de clé recherchée, ce bloc touvée on le met dans le cache disque puis:
- si l'index est clusterisé, le fichier contient à la suite les autres blocs qui satisfont la valeur de clé. --> On met dans le cache tous les blocs jusqu' à ne plus trouver de blocs qui nous interesse.

-si l'index est non-clusterisé:  après avoir le premier bloc en cache, on revient à la feuille de l'index et on recommence la même opération pour tous les pointeurs de l'index.

Est - ce exact?

B)Dans le cas d'un index non clusterisé, peut il y avoir un ensemble d'identifiant (donc leurs adresses sur le disque) de pages associé à une valeur de clé (si on a plusieurs occurence d'une même valeur dans la table, exemple : plusieurs tuples ont la valeur 5)?

C Vous avez dit que c'est le sytème d'exploitation qui lit les pages. Cela veut -t-il dire que dans le cas d'un select c'est l'OS qui fait les lectures sur disque, contrairement au cas d'une insertion où ce sont les processus postgres et bgwriter qui lisent sur le disque?

Ensuite, pour les statistiques collectées, c'est normal: les index eux restent triés par rapport à leur clé, c'est le principe du btree. C'est pour cela que vous avez les mêmes statistiques de lecture sur l'index. C'est sur la table (heap) que les accès seront différents.

D)
Ah oui, donc en fait  idx_blks_read est le nombre de blocs de l'index qui pour etre lu ont necessité un acces au disque, idx_blks_hit est le nombre de blocs dont le contenu a pu etre trouve dans le cache associe à l'index correspondant.

J'ai donc regardé dans pg_statio_all_tables, les colonnes heap_blks_read et heap_blks_hit.

En recherchant cette fois pour colid =90000 (je change la valeur au cas où postgres se souviendrait d'une execution), on a donc toujours 5 occurences trouvées (d'apres le remplissage des tables) et on a les valeurs de colonnes suivantes:

En utilisant l'index cluster:
heap_blks_read = 5 heap_blks_hit = 0 -->  je l'interprète comme : il a fallu autant de lectures sur disques pour trouver les blocs contenant les resultats.
idx_blks_read = 2  idx_blks_hit = 1 --> je l'interprète comme : pour trouver la page correspondant à la clé il a fallu lire 2 pages de l'index sur le disque et une dans le cache

En utilisant l'index non clusterisé :
heap_blks_read = 5 heap_blks_hit = 0  --> si on a heap_blks_read , il est possible qu'aucune page supplémentaire n'ait été lu (si les 5 enregsitrement sont dans des pages différentes), donc il est possible que dans le readahead n'ait pas été utilisé. Est-ce exact?
idx_blks_read = 2  idx_blks_hit = 2 

Les accès disques sur le fichier de données sont donc similaires. Savez vous d'où viens le problème, ou faut-il changer autre chose?

Je réalisé sur ces tests sur ces tables, car sur des  table de ma base de données de travail, j'avais des temps d'execution que j'utilise un index (sur un champ integer) btree cluster ou non cluster, et que la colonne pouvoit contenir plusieurs fois la même valeur. (Comme c'est le cas pour test_cluster et test_idx).

voici comment a été crée le cluster:

CREATE INDEX idx_cluster
  ON test_cluster
  USING btree
  (colid);
ALTER TABLE test_cluster CLUSTER ON idx_cluster;

merci à vous

Dernière modification par kris_le_parisien (02/05/2011 17:13:34)

Marc Cousin

Membre

Re : Tri physique des données

A) Non, ce n'est pas exact. Un index clusterisé n'est qu'un index normal. Il n'a rien de plus. Simplement, les pages de données contenant des valeurs contiguës sont contigues. Donc sont accédées de façon efficace (car déjà en cache). Par ailleurs je ne vois pas la distinction que vous faites entre une page et un bloc de table. En tout cas l'index pointe sur ce qu'on appelle dans postgres un tid (tuple id), qui est le numéro de bloc, et le numéro d'enregistrement dans ce bloc.
B) Non, si vous avez 50 valeurs '5' dans la table, vous avez 50 valeurs '5' dans l'index pointant vers ces 50 enregistrements. Ce sont bien 50 enregistrements différents.
C) Non, c'est toujours le système d'exploitation qui accède au disque, en lecture comme en écriture. PostgreSQL accède à des fichiers. Simplement, quand postgres demande un bloc au système d'exploitation en lecture, le système demande plusieurs blocs d'un coup au disque, et les met en cache, en pariant sur le fait que postgres va lui demander juste après. C'est ce qu'on appelle read-ahead (lecture anticipée)
D) À mon avis, il y a une légère différence entre les deux tables: l'une a un index qui fait une page de plus. De toutes façons, on ne voit pas les effets du clustering sur une table de 5 blocs… il faut qu'elle fasse plusieurs gigas, habituellement.

---

Marc.

kris_le_parisien

Membre

Re : Tri physique des données

Par ailleurs je ne vois pas la distinction que vous faites entre une page et un bloc de table

1))Donc est-ce que une page est la même chose qu'un bloc, et la taille d'une page est donc 8 Ko?

D) À mon avis, il y a une légère différence entre les deux tables: l'une a un index qui fait une page de plus. De toutes façons, on ne voit pas les effets du clustering sur une table de 5 blocs… il faut qu'elle fasse plusieurs gigas, habituellement.

Les 2 tables contiennent 500 000 lignes.
Est ce que le problème est que je ne demande que 5 lignes (puisque chaque valeur est présente 5 fois dans la table). Si une valeur est présente disons 10 fois dans la table, est-ce que cela changera quelque chose?
2) Pourquoi a -t-on heap_blks_read = 5 heap_blks_hit = 0 avec l'index cluster?
On sait qu'il y a 5 pages feuilles qui pointe vers les 5 enregistrements souhaités, et que une fois la première page feuille trouvée, postgres demande à l'OS le bloc correspondant à l'enregistrement [b](via le tid du bloc), et l'OS charge dans le cache plusieurs blocs consécutifs ( d'après ce que j'ai compris du read-ahead (lecture anticipée)). Or comme les blocs que l'on cherche sont consecutifs dans le fichier de données, ils devraient être tous (ou presque) dans le cache qui fait quand même 32 Mo, on devrait donc avoir au moins  heap_blks_hit >1 (pour au moins un bloc de données trouvée dans le cache, sans passé par le pointeur d'une page feuille).

Où est-ce que cela coince?

3)On dirait qu'il n'exploite pas le fait que le fichier de données soit trié par rapport aux feuilles de l'index (c'est bien par rapport aux feuilles?). Est- ce qu'il le fait seulement si le fichier de données est très gros, c'est à dire plusieurs GB?

4) Pourtant si je demande un intervalle de valeurs, là je vois que c'est plus rapide avec l'index cluster. Exemple: colid> 250 AND colid <40

merci pour votre aide.

Dernière modification par kris_le_parisien (02/05/2011 19:02:46)

Marc Cousin

Membre

Re : Tri physique des données

1) Oui
2) Oups, effectivement, la table est un peu plus grosse
Sinon, entre 5 et 10 fois vous ne verrez probablement pas de différence notable. Faites plutôt le test sur une étendue plus élevée (par exemple toutes les valeurs entre x et y).
heap_blks_read, c'est le nombre de demandes de lectures que postgres fait au système d'exploitation. Vous ne verrez pas le read-ahead apparaitre, puisque ça se produit au niveau du système d'exploitation. heap_blks_read = 5, cela signifie qu'il y a 5 entrées d'index qui pointent vers 5 enregistrements, qui se trouvent dans 5 pages différentes. Jusque là, ça va, votre raisonnement se tient. Par contre, après, postgresql ne voit pas le read-ahead. Il demande les blocs suivants au système d'exploitation, qui les trouve dans son propre cache. 'read', ça veut dire que postgres n'a pas trouvé les données dans son propre cache.
Le reste découle de ça. L'effet du cluster, vous ne le verrez pas dans les statistiques postgres, vous le verrez dans les performances de la requête, et la réduction du nombre d'entrées sorties au niveau du système.

---

Marc.

kris_le_parisien

Membre

Re : Tri physique des données

Merci beaucoup,

il faut avouer que c'est pas évident au début

J'ai donc testé le même principe sur des tables plus grosses.

J'ai deux tables qui possèdent les mêmes lignes: rel_kohonen_sig et copie_rel_kohonen_sig,  6 578 730 lignes chacunes
attributs des deux tables :(id_kohonen, size_x, size_y, map, n_fun, id_signature). On a size_x= size_x, map='TOR'  et n_fun = 'Gaus' pour chaque ligne

Je crée un index non clustérisénsur size_x pour rel_kohonen_sig
et un  index  clustérisé sur size_x pour copie_rel_kohonen_sig.

Je lance la requête suivante:
SELECT  size_x  FROM rel_kohonen_sig where size_x =10; --> temps = 1873.406 ms, heap_blks_read = 3310, heap_blks_hit=0, idx_blks_read = 1126 et idx_blks_hit=0
SELECT  size_x  FROM copie_rel_kohonen_sigwhere size_x =10 -->temps = 3640.761 ms , heap_blks_read = 41904, heap_blks_hit=0, idx_blks_read = 1126 et idx_blks_hit=0

Il y a-t-il encore un autre problème qu'il fallait regarder?
Ca devrait etre l'inverse?

Marc Cousin

Membre

Re : Tri physique des données

C'est anormal: sur la copie, vous avez 42000 lectures de blocs, contre 3300 pour l'original. Vous devriez en avoir moins, ou le même nombre. Il y a quelque chose d'autre… Pouvez-vous me donner la procédure exacte pour créer l'index clusterisé ?

---

Marc.

kris_le_parisien

Membre

Re : Tri physique des données

Bonjour,

La raison était en fait la manière dont j'avais rempli la copie de la table originale.
J'ai recréé des tables pour faire des tests:
copie_rel_kohonen_sig, copie_rel_kohonen_sig2, sont des copies de rel_kohonen_sig.

sur copie_rel_kohonen_sig on a mis un index btree cluster et sur copie_rel_kohonen_sig2 un index btree non cluster
Exemple de création:
create table nom_table  as
select * from rel_kohonen_sig

Par contre copie_random_rel_kohonen_sig :
create table copie_random_rel_kohonen_sig as select * from rel_kohonen_sig order by random() --> le order by random() avait été rajouté au départ pour avoir une chance                             
                                                                                                                                            d'une meilleure repartition des données dans la table
on met un index btree cluster sur copie_random_rel_kohonen_sig

tous sauf copie_random_rel_kohonen_sig  ont les meme temps , c'est à dire 2590 ms et  heap_blks_read = 3310, heap_blks_hit=0, idx_blks_read = 1126 et idx_blks_hit=0

copie_random_rel_kohonen_sig prend  9417 ms heap_blks_read = 41903, heap_blks_hit=0, idx_blks_read = 1127 et idx_blks_hit=0

Mais donc les tables avec index cluster ne sont pas plus rapides.
Dans chaque table, on a 41117 lignes qui ont pour valeur size_x = 10.

Pourquoi les tables avec index clusterisé ne donnent pas de meilleurs temps?

merci beaucoup

Dernière modification par kris_le_parisien (02/05/2011 23:55:30)

Marc Cousin

Membre

Re : Tri physique des données

Comment créez vous l'index clusterisé ?

---

Marc.

kris_le_parisien

Membre

Re : Tri physique des données

Je crée les tables et index via pgadmin3, et je lance les requêtes via la console psql (que l'on accède grâce au bouton pluggin).

sur copie_rel_kohonen_sig  l'index clusterisé est crée par le code sql ci-dessous généré automatiquement par pgadmin3:

CREATE INDEX idx_cluster_size_x
  ON copie_rel_kohonen_sig
  USING btree
  (size_x);
ALTER TABLE copie_rel_kohonen_sig CLUSTER ON idx_cluster_size_x;

Est-ce qu'il y a quelque chose que j'ai oublié en créant mon index?

kris_le_parisien

Membre

Re : Tri physique des données

J'ai essayé une autre syntaxe, pour le cluster mais qui semble presque marcher.

J'ai crée une table copie_cluster2   à partir de rel_kohonen_sig :             create table copie_cluster2 as select * from rel_kohonen_sig
Je lui ai alors ajouté un index idx_btree_rel_kohonen_sig ,btree non clusterisé:
CREATE INDEX idx_btree_rel_kohonen_sig
  ON copie_cluster2
  USING btree
  (size_x);

J'ai ensuite exécuté la commande suivante : Cluster copie_cluster2  using idx_btree_rel_kohonen_sig

Dans pg_statio_all_tables: j'obtiens alors les resultats suivants: heap_blks_read = 2620  , heap_blks_hit=0, idx_blks_read = 1127 et idx_blks_hit=0

Mais malgré une plus faible valeur de heap_blks_read, le temps d'execution dans la console psql est de 1903.33 ms, soit quasiment la même chose.

Avez-vous une idée?
(Les requêtes select sont executés dans la console psql)

Marc Cousin

Membre

Re : Tri physique des données

La seconde syntaxe (cluster on) est la bonne. La première déclare l'index comme étant cluster, mais ne trie pas physiquement la table.

Il est très possible que le gain soit assez faible parce qu'une partie des données se trouve dans le cache du système d'exploitation. C'est assez dur à mesurer.

---

Marc.

kris_le_parisien

Membre

Re : Tri physique des données

Voulez vous plutot dire que cluster nom_table using nom_index est la bonn solution?

Il apparait que cluster on ne trie effectivement pas la table. Mais que signifie déclarer l'index comme cluster, vu qu'il n'y a pas de type cluster?

Malgré tout est-ce que cluster on peut servir à quelque chose? Je me rappelle que quand j'utilisais les index déclarés avec cluster on, rendaient les requêtes plus rapide lorsque l'on faisait un select sur un ensemble de valeurs de clés?
--> SELECT  size_x  FROM rel_kohonen_sig where size_x >25 AND size_x <40.

Malgré tout il y a un gain non négligeable. J'ai fait les tests sur mon ordi personnel, peut-etre que sur un autre ordinateur cela sera plus rapide.

Concernant le order by random ,  ce n'est visiblement pas la bonne solution pour répartir aléatoirement des données. (Comme marqué plus haut cela ralentit par la suite les temps d’exécution)
Connaissez- vous une manière qui à partir d'une table crée une autre table avec les mêmes lignes mais en disposant les lignes de manière aléatoire.?

Encore un grand merci pour votre aide précieuse

Dernière modification par kris_le_parisien (03/05/2011 10:52:45)

Marc Cousin

Membre

Re : Tri physique des données

cluster trie effectivement la table. C'est le alter table qui ne fait rien.

Déclarer l'index comme 'cluster' ne sert qu'à une chose: pouvoir taper «cluster table» sans préciser sur quel index (il comprend automatiquement), ou «cluster», qui re-clusterise toutes les tables qui ont un index déclaré comme cluster. Ce n'est qu'une déclaration dans le catalogue. Elle est d'ailleurs aussi faite, bien sûr, quand on utilise la syntaxe complète de cluster.

---

Marc.

kris_le_parisien

Membre

Re : Tri physique des données

Bonjour,

sur copie_rel_kohonen_sig on a mis un index btree cluster et sur copie_rel_kohonen_sig2 un index btree non cluster
Exemple de création:
create table nom_table  as
select * from rel_kohonen_sig

Par contre copie_random_rel_kohonen_sig :
create table copie_random_rel_kohonen_sig as select * from rel_kohonen_sig order by random() --> le order by random() avait été rajouté au départ pour avoir une chance                             
                                                                                                                                            d'une meilleure repartition des données dans la table
on met un index btree cluster sur copie_random_rel_kohonen_sig

tous sauf copie_random_rel_kohonen_sig  ont les meme temps , c'est à dire 2590 ms et  heap_blks_read = 3310, heap_blks_hit=0, idx_blks_read = 1126 et idx_blks_hit=0

copie_random_rel_kohonen_sig prend  9417 ms  heap_blks_read = 41903, heap_blks_hit=0, idx_blks_read = 1127 et idx_blks_hit=0

la table copie_random_rel_kohonen_sig  tel qu'elle a été construite via le order by random(), augmente le nombre de lectures sur disque.
Connaissez- vous une fonction qui permet de répartir aléatoirement les données d'une table lors de sa création ç partir d'une autre table?
merci bien

Marc Cousin

Membre

Re : Tri physique des données

Le order by random doit le faire. Mais ce n'est pas vraiment le genre de choses qu'on essaye de faire habituellement sur une base de données

---

Marc.

kris_le_parisien

Membre

Re : Tri physique des données

Bonjour,

J'ai refait des tests des index clusterisés et non - clustérisés. Je l'ai fait cette fois sur une table triée selon l'attribut de recherche. Je remarque alors que le comportement des deux index et les temps d'execution dans les deux cas sont exactement les mêmes.

Situation:  J'ai une table rel_kohonen_sigtriee, table triée sur l'attribut size_x. 

create table  rel_kohonen_sigtriee
as select * from rel_kohonen_sig order by size_x;

Je crée deux copies de cette table:  copieclustersurtriee et copieidxsurtriee. Ces deux tables sont donc identiques à ce moment là.

Je crée alors les deux index btree suivant: idx_clustertriee_size_x et idx_copieidxsurtriee.

copieclustersurtriee est alors triée selon idx_clustertriee_size_x.

En fait comme vous l'aviez dit , je crois precedemment, les deux index sont les mêmes. La seule chose qui change c'est que copieclustersurtriee est réorganisée physiquement et pas copieidxsurtriee.

Je lance la requête suivante sur les deux tables:

select * from nom_table  where size_x =12;
J'obtiens alors les mêmes résultats pour les deux copies --> temps = 2389,562 ms, heap_blks_read = 2620, heap_blks_hit=0, idx_blks_read = 1128 et idx_blks_hit=0

Pourtant  copieclustersurtriee a été trié selon son index et pas copieidxsurtriee. 
A) Peut-on en déduire que les noeuds feuilles des index sont ordonées de gauche à droite dans l'ordre croissant?

B) On a heap_blks_read = 2620

Les lectures du fichier de données correspondent -t-ils alors simplement au fait qu'au bout d'un moment le cache est plein et donc on écrit tous les tampons qu'il contient sur le disque, et ensuite on peut mettre dans le cache les tuples du fichier de données correpondant au noeud feuille suivant de l'index.

C) Ainsi si on a un fichier de données triés selon l'attribut de recherche, est ce que dans tous les cas quand on utilise un index btree, que l'index soit clustérisé ou pas (donc que le fichier de données soit réorganisé ou pas), on aura forcément les mêmes temps d'execution.

Est-ce que les affirmations écrites ci-dessus sont corrects?

Question auxilliaires
(est- ce que la clé d'un noeud non-feuille est dans ce cas 12 concaténé à adresse physique du premier bloc du fichier de données, tel que la ligne de la relation correspondant à ce numéro de bloc ait pour valeur size_x=12?)

merci beaucoup

Dernière modification par kris_le_parisien (03/05/2011 18:36:52)

Marc Cousin

Membre

Re : Tri physique des données

A) Les noeuds feuille sont ordonnés par ordre croissant. C'est bien le principe d'un btree. De gauche à droite, ça n'a pas vraiment de sens
B) Vous n'avez pas l'air d'avoir bien compris comment fonctionne le cache. Je ne vois pas le rapport entre le cache plein et le fait qu'on écrive tous les tampons qu'il contient sur disque. Tant que vous n'avez pas de modification de données, il n'y a aucune raison d'écrire sur disque
C) Je pense que ce que vous ne comprenez pas, depuis le début, c'est qu'un index clusterisé n'est qu'un index normal. Clusteriser une table, c'est juste la trier physiquement par rapport à l'ordre de l'index. L'index, lui, reste le même, techniquement. La seule différence, c'est que postgres sait que la table a été clusterisée sur l'index, et prend donc cette information en compte dans les plans d'exécution (il sait qu'il aura probablement beaucoup moins de lectures physiques à effectuer que sur une table non clusterisée si il passe par l'index). Par ailleurs, une table clusterisée ne le reste, les nouveaux enregistrements sont mis où on peut.

---

Marc.

kris_le_parisien

Membre

Re : Tri physique des données

Bonjour,

merci de votre réponse.

A) Oui en fait ce que je voulais savoir c'est que si on a triée une table pour une de ces colonnes dans l'ordre croissant, alors elle est triée de la même façon que les noeuds d'un arbre B-Tree.
B)  En fait ce que je voulais dire c'est qu'ici la taille du cache est de 32 Mo. Mais rien n'empêche la table d'être plus grosse.  Du coup lors d'une requête SELECT sur la table rel_kohonen_sigtriee , au moment où l'on a copiee 4096 blocs de 8 Ko de cette table dans le cache (le shared_buffer), alors on a que le cache est plein . Donc pour charger les prochaines lignes de rel_kohonen_sigtriee dans le cache, il faut peut -etre d'abord écrire sur le disque (dans un fichier temporaire par exemple)les tampons déjà présents dans le cache et une fois cela fait, remplacer les valeurs de ces tampons (que l'on a cherché à modifié, donc usage_count a été décrémentée jusqu'à atteindre 0) par les prochaines lignes de la table rel_kohonen_sigtriee . Le resultat retourné est alors le contenu des fichiers temporaires + le contenu du cache à la fin de l'execution. 
Je voulais donc savoir si c'était le bien le comportement qui se produit.

C) J'ai bien compris que l'index clustérisé n'était pas un autre type d'index, et que c'était le fichier de données qui était réorganisé.  Mais dans notre dernier cas où la table de données est triée, on a exactement les mêmes résultats (temps, nombre de blocs lus sur le disque,dans le cache....) que le fichier de données ait été réorganisé (vu que le fichier était déjà trié, je pense que ça ne change rien) ou pas.
Je me suis donc dit que dans les deux cas on a un comportement similaire

(comme supposé plus haut, on part d'un noeud feuille puis on lit le fichier sur le disque, on met les enregistrements dans le cache, et tant que l'on trouve dans le cache le tuple correspondant au tid d'un enregistrement d'une page feuille de l'index alors on ne fera pas daccès disque à partir de son pointeur et on vérifie alors l'enregistrement suivant de la page ou de la page suivante...),   --> C'est peut-etre cela qui est un peu floue

et l'information comme quoi une des copies de la table est réorganisée sur son index n'apporte dans ce cas aucun gain.
En fait je n'arrive pas trop à expliquer le fait que l'on ait les même résultats dans les deux cas, et je voulais savoir si dans tous les cas où l'on a une table triée par ordre croissant peut on dire que clusteriser une relation sur son index n'apportera aucun gain de performances.

Désolé d'insister mais je voudrais vraiment bien comprendre comment cela se passe, et je ne trouve pas beaucoup d'endroits sur le net expliquant clairement le déroulement des actions.

En vous remerciant de toute votre aide

Dernière modification par kris_le_parisien (04/05/2011 10:27:56)

Marc Cousin

Membre

Re : Tri physique des données

A) Oui, l'ordre est le même. C'est le but.
B) Non, quand le cache est plein, on supprime une page du cache. Pour ça, on l'écrit sur le disque, si elle était modifiée. Sinon, on la supprime simplement du cache. Vu vos tests, on la supprime simplement du cache. Sachant qu'elle restera certainement dans le cache du système d'exploitation.
C) Une table triée par un SELECT, puis indexée, ou une table triée par CLUSTER, c'est exactement le même résultat sur le disque. Donc le même plan, la même consommation de blocs, etc…

---

Marc.