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,

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

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

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?

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?

Bonjour,
merci de votre reponse,

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?

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

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.

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

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.

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.

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

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"