Vous n'êtes pas identifié(e).
merci beaucoup pour vos réponses 
D'accord,
1) donc le cache de Postgres est vidée de 1 page. J'ai lu qu'un tampon peut être modifié même dans une requête SELECT. Comment savez-vous que dans mon cas les tampons ne sont pas modifiés mais simplement supprimés? Est-ce que la modification d'un tampon survient uniquement (dans le cas où la requête est un Select ) quand plusieurs utilisateurs veulent accéder par un SELECT aux mêmes données?
2) Quand un tampon est supprimée du cache de Postgres, le résultat est toujours présent dans le cache de l'OS car lorsque l'on récupère une donnée sur le disque elle est enregistrée dans le cache de l'OS puis dans celui de Postgres. Donc pour retourner les résultats aux utilisateurs on récupérera une partie des données dans le cache de L'OS.
Est-ce exacte?
Merci à vous
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
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
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_sigPar 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_sigtous 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
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
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)
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?
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
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?
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.
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
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;
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.
Pour les résultats depuis mon ordinateur personnel, j'ai d'abord lancé pgadmin 3, puis grâce au bouton "pluggin" j'ai ouvert le terminal pour psql. J'ai alors fait les tests depuis ce terminal.
Sur le lieu de travail, tout s'est fait depuis le terminal (connexion a la base de données puis test).
Bonjour,
on appellera R1: la requête: Select * from rel_kohonen_sig where size_x = 10;
et R2: select id_kohonen_map,id_signature, size_x,size_y,map_type,neighborhood_function
from signature, kohonen_map, signature_has_kohonen_map where
signature.id_signature=signature_has_kohonen_map.signature_id_signature and
signature_has_kohonen_map.kohonen_map_id_kohonen_map=kohone
and size_x=10;
J'ai testé les requêtes via la console psql. J'ai deux cas:
-lors d'un test sur mon ordinateur personnel (qui possede une copie de la base) , R1 donne 2516,133 ms et R2 donne 38 573,545 ms.
On peut dire que c'est environ 10 fois le temps des EXPLAIN ANALYZE. Le temps de rapatriement des resultats est-il a l'origine de cette difference? Peut on diminuer ce temps?
j'ai essaye d'augmenter default_statistics_target de 100 à 1000 puis 10 000--> les requetes R1 et R2 vont alors plus vite, mais si je demande toutes les lignes (on enleve la condition "size_x=10") les requêtes ont un temps d'execution beaucoup plus long. Ca ne semble pas un bon compromis.
-lors d'un test sur le lieu de travail: on obtient R1 donne 2516,133 ms et R2 donne 5723,545 ms.
J'avais modifie le parametre work_mem de 1 a 5MB (dans postgresql.conf ), cela peut il expliquer la diminution de l'ecart?
Merci a vous
Mais j'ai lancé les 2 requêtes via pgadmin. Pourquoi est-ce que la différence de temps n'est pas conservé?
Si je lance depuis un terminal, est-il possible que je vois la différence de temps?
merci beaucoup
Je précise que j'ai fait plusieurs fois un vacuum analyze après la création des index, de la table et de son remplissage.
Il y a -t-il un moyen d'évaluer le temps de récuppération sur le réseau depuis pgadmin 3?
La cause éventuelle de ce temps de ce récupération est-il que le réseau est trop chargé? Sinon, connaissez vous les raisons les plus probables?
Est-ce qu'on ne peut rien faire pour améliorer ce temps à partir de pgadmin?
merci bien.
Je ne sais pas. Lors d'un précédent message vous aviez dit qu'un lien lent entre le serveur et la machine, ou un grand volume de données pouvait être la cause.
Bonjour,
Voici ma situation. J'ai trois tables:
signature(id_signature,...autres paramètres) --> 1 645 070 lignes
signature_has_kohonen_map --> (signature_id_signature,kohonen_map_id_kohonen_map)--> 6 578 784 lignes
kohonen_map --> (id_kohonen_map, size_x, size_y, map, n_fun,...autres paramètres)--> 144 lignes.
Je veux effectuer la requête suivante:
SELECT *
FROM signature, sign_koh, kohonen where size_x= 10 AND sizeconditions_de_jointures_sur_clés_des_les_tables
La requête s'exécute en 31234 ms pour 411174 lignes.
La jointures donne un total de 6 578 784 lignes. Il faut préciser qu'il y a 16 valeurs différentes de size_x, et que chaque valeur est présente dans 411174 lignes différentes du résultat de la jointure. (6.25% de la table)
Pour essayer de réduire ce temps, j'ai crée une table rel_kohonen_sig où j'ai inséré le résultat de la requête précédente. J'ai ensuite mis un index sur la colonne size_x de cette table.
Mais quand j'exécute la requête select sur rel_kohonen_sig, elle à un temps équivalent à celle appliquant sur la jointure des tables. Pourtant l'index que j'ai créé est bie utilisé.
Savez-vous si c'est normal, ou si il y a besoin de modifier quelque chose?
Voici les arbres obtenus: (ne pas prendre en compte les temps indiqués dans Total runtime, ils sont largement inférieurs au temps réel d'exécution de la requête)
pour la table rel_kohonen_sig:
"Bitmap Heap Scan on rel_kohonen_sig (cost=7720.06..54777.44 rows=412270 width=24) (actual time=60.583..166.575 rows=411174 loops=1)"
" Recheck Cond: (size_x = 10)"
" -> Bitmap Index Scan on size_x_index_rks_table (cost=0.00..7617.00 rows=412270 width=0) (actual time=59.703..59.703 rows=411174 loops=1)"
" Index Cond: (size_x = 10)"
"Total runtime: 219.340 ms"
pour la jointure de tables:
"Hash Join (cost=50725.88..193876.37 rows=411174 width=24) (actual time=1433.593..5263.581 rows=411174 loops=1)"
" Hash Cond: (signature_has_kohonen_map.signature_id_signature = signature.id_signature)"
" -> Hash Join (cost=4.91..123684.93 rows=411174 width=24) (actual time=272.374..2979.550 rows=411174 loops=1)"
" Hash Cond: (signature_has_kohonen_map.kohonen_map_id_kohonen_map = kohonen_map.id_kohonen_map)"
" -> Seq Scan on signature_has_kohonen_map (cost=0.00..94897.84 rows=6578784 width=8) (actual time=0.023..1240.166 rows=6578784 loops=1)"
" -> Hash (cost=4.80..4.80 rows=9 width=20) (actual time=0.084..0.084 rows=9 loops=1)"
" -> Seq Scan on kohonen_map (cost=0.00..4.80 rows=9 width=20) (actual time=0.022..0.073 rows=9 loops=1)"
" Filter: (size_x = 10)"
" -> Hash (cost=23730.65..23730.65 rows=1645065 width=4) (actual time=1160.420..1160.420 rows=1645065 loops=1)"
" -> Seq Scan on signature (cost=0.00..23730.65 rows=1645065 width=4) (actual time=0.014..500.493 rows=1645065 loops=1)"
"Total runtime: 5320.108 ms"
merci
En effet,
quelque soit la stratégie de stockage utilisé, on a toujours les mêmes temps d’exécution.
merci beaucoup pour vos réponses
oui je crois que c'est ça. Pour chacune de mes tables contenant des éléments de type bytea, une table TOAST lui est associé.
- J'ai vu qu'il y avait aussi l'insertion d'objets larges (dans pg_largeobject) . Je voudrais savoir si cela ne concernait que l'insertion de fichier depuis un autre programme (exemple une image au format jpeg depuis une application écrite en C) dans la base?
- Si il y a un moyen d'utiliser cette méthode, pour juste insérer de grosses données (comme de volumineux résultats de calculs, mais pas des fichiers), cela serait - t - il plus efficace que la méthode TOAST?
- Dans ma table sig_gab: La colonne Sig_vector de type bytea a un stockage de type EXTENDED.
Si je change le type de stockage pour EXTERNAL, mes requêtes (SELECT surtout) sur cette colonne sera -t- elle plus rapide, malgré un espace de stockage plus grand (corrigez- moi au cas où j'ai mal compris la définition )?
Si l'on choisit la méthode EXTERNAL, il y a -t-il un moyen d'estimer l'espace de stockage liée à la table TOAST? (Etant donnée n lignes dans la table sig_gab (sig_vector est non nulle), et qu'il est indiqué largeur moyenne de colonne sig_vector = 263)?
merci d'avance
Bonjour,
j'aurais une question à propos des colonnes contenant des valeurs très grosses.
J'ai ainsi une colonne appelée sig_vector contenant des valeurs de type bytea:
exemple de la valeur d'une ligne:
1.11498E+02, 1.35706E+02, 2.88843E+02, 1.30191E+02, 1.89009E+02, 1.88747E+02, 1.16372E+03, 1.71711E+02, 1.84266E+02, 2.83373E+02, 2.02455E+03, 2.50216E+02, 2.94586E+02, 4.01803E+02, 2.74225E+03, 3.59621E+02, 2.37780E+02, 4.26430E+02, 4.90676E+03, 3.64694E+02
Quelque soit la requête SELECT, quand je rajoute la colonne sig_vector, ça me ralentit énormément les temps d'exécution.
Sauriez- vous si en modifiant la valeur d'un paramètre on peut diminuer ce temps?
Exemple:
SELECT id_image, id_signature, norm_x, norm_y, scale_numbers, quadrant ---> 11 279 ms en ayant augmenté le paramètre work_mem à 5MB
FROM sig_gab,signature, image
where signature.image_id_image = image.id_image
and signature.id_signature = sig_gab.signature_id_signature order by time
SELECT id_image, id_signature, norm_x, norm_y, scale_numbers, quadrant,sig_vector --> 55 450 ms quelque soit la valeur de work_mem
FROM sig_gab,signature, image
where signature.image_id_image = image.id_image
and signature.id_signature = sig_gab.signature_id_signature order by time;
résultats du explain analyse:
"Sort (cost=66346.27..66685.69 rows=135769 width=292) (actual time=1671.917..1869.361 rows=137058 loops=1)"
" Sort Key: image."time""
" Sort Method: external merge Disk: 40928kB"
" -> Hash Join (cost=7606.65..36207.42 rows=135769 width=292) (actual time=135.894..1105.050 rows=137058 loops=1)"
" Hash Cond: (signature.image_id_image = image.id_image)"
" -> Merge Join (cost=8.85..15113.99 rows=137058 width=284) (actual time=0.101..568.280 rows=137058 loops=1)"
" Merge Cond: (sig_gab.signature_id_signature = signature.id_signature)"
" -> Index Scan using fk_sig_gab_signature on sig_gab (cost=0.00..9058.56 rows=137058 width=280) (actual time=0.058..184.511 rows=137058 loops=1)"
" -> Index Scan using signature_pkey on signature (cost=0.00..50013.17 rows=1645065 width=8) (actual time=0.036..170.357 rows=137059 loops=1)"
" -> Hash (cost=5214.58..5214.58 rows=137058 width=12) (actual time=135.720..135.720 rows=137058 loops=1)"
" -> Seq Scan on image (cost=0.00..5214.58 rows=137058 width=12) (actual time=0.014..76.676 rows=137058 loops=1)"
"Total runtime: 1939.968 ms"
Ok. merci.
Pour les signatures, chacune d'elles a identifiant unique (on a crée cette relation, pour identifier de manière unique les résultats de chaque algorithme). Il n'y a donc qu'un seul ID pour une signature. Je n'ai pas très bien compris quand vous dites stocker directement la signature.
J'ai oublié de préciser que toutes les signatures ne sont pas toutes utilisées (le véritable mot est: entraînées) par un cluster. C'est le cas pour seulement certaines d'entre elles. 1 signature peut être utilisée par plusieurs clusters.
Votre suggestion était peut-être de placer les attributs de signatures directement dans ClustersSignature. Mais la précision que j'ai rajoutée l'en empêche, je pense.
merci beaucoup pour votre aide.
