Forums PostgreSQL.fr

kris_le_parisien

Membre

Index inéfficace

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

Marc Cousin

Membre

Re : Index inéfficace

C'est un peu difficile à suivre.

=> Pourquoi est-ce que le total runtime est très inférieur au temps réel ? (c'est louche)

---

Marc.

kris_le_parisien

Membre

Re : Index inéfficace

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.

Marc Cousin

Membre

Re : Index inéfficace

Oui. Ce qui laisserait supposer que si le temps total entre les deux est similaire, alors que le temps d'exécution de la requête elle même est dans un cas de 200ms et dans l'autre de 5s, c'est que le temps d'exécution est négligeable par rapport au temps de récupération sur le réseau par exemple.

Parce que les plans montre bien un rapport 50 entre les deux temps d'exécution (ce qui est logique, vous avez précalculé le résultat de la requête).

---

Marc.

kris_le_parisien

Membre

Re : Index inéfficace

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.

kris_le_parisien

Membre

Re : Index inéfficace

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.

Marc Cousin

Membre

Re : Index inéfficace

Ah ok, je ne savais pas que vous affichiez via pgadmin. Le temps, c'est probablement seulement le temps de dessiner un tableau de 400 000 enregistrements. pgadmin n'est pas vraiment fait pour ça, et est assez lent quand il a à le faire.

---

Marc.

kris_le_parisien

Membre

Re : Index inéfficace

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

Marc Cousin

Membre

Re : Index inéfficace

- Quels sont les temps réels dans ce cas (via pgadmin)

- Possible. Mais n'affichez pas le résultat, envoyez le vers un fichier (avec un \o par exemple).

Les temps par explain analyze ne sont pas totalement fiables, parce que le moteur doit récupérer de nombreuses fois la date (ce qui est plus ou moins rapide suivant les composants permettant de récupérer l'heure sur la machine), afin de mesurer les durées réelles de chaque étape. Mais ils restent malgré tout dans le bon ordre de grandeur.

Dernière modification par Marc Cousin (06/04/2011 17:28:16)

---

Marc.

kris_le_parisien

Membre

Re : Index inéfficace

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

Marc Cousin

Membre

Re : Index inéfficace

Pour les temps d'affichage, est-ce que ce sont toujours les temps d'affichage sous pgadmin ? Dans les deux tests ?

---

Marc.

kris_le_parisien

Membre

Re : Index inéfficace

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).

Marc Cousin

Membre

Re : Index inéfficace

Alors je pense que la principale différence pour le temps d'exécution pourrait provenir du fait que la machine du lieu de travail a davantage de mémoire ? Le résultat d'une requête ayant énormément de résultat est stocké en mémoire côté client, avant d'être affiché.

---

Marc.