Vous n'êtes pas identifié(e).
bonjour,
j'ai une table "table_1" de plus de 500.000 enregistrements et 150 champs (dont ident qui est la pk)
j'ai transformé la table_1 en une table "table_2" comportant 3 colonnes
ident,nom_champs,valeur:
ident est l'ident de la table_1
nom_champs est l'un des 150 champs de la table table_1
valeur est la valeur d'un champs donné pour un ident donné
par ex dans la table_1
ident champs1 champs2 .... champs150
id01 val1_1 val1_2 .... val1_150
id02 val2_1 ......
dans la table table_2 on aura
ident nom_champs valeur
id01 champs1 val1_1
id01 champs2 val1_2
...
id01 champs150 val1_150
id02 champs1 val2_1
...
j'ai rajouté sur table_2 2 index l'un sur ident l'autre sur nom_champs
je réalise la requête sur la table_1 de la forme:
SELECT sum(champsn) FROM table_1;la requête s’exécute en 3 sec
sur la table_2 j’exécute la requête équivalente qui me donnera le même résultat
SELECT sum(valeur) FROM table_2 WHERE nom_champs='champsn'la requete s'execute en 120sec
je pensais q'un sgbd était optimisé pour des tables avec peu de colonnes même avec beaucoup d'enregistrements
pourquoi observe t'on tant de différence?
Hors ligne
Bonjour.
Effectivement le temps de traitement n'est pas normal.
Avez-vous regardé si l'index sur nom_champs était utilisé lors de la requête avec un explain ?
Un analyze de la table table_2 peut aussi aider après sa création.
Julien.
https://rjuju.github.io/
Hors ligne
bonsoir, voici l'explain
"Aggregate (cost=249305.10..249305.11 rows=1 width=2)"
" -> Bitmap Heap Scan on t_donnees_fr1 (cost=9839.48..245365.11 rows=525331 width=2)"
" Recheck Cond: (varcod = 76)"
" -> Bitmap Index Scan on varcod_t_donnees_fr1_idx (cost=0.00..9708.14 rows=525331 width=0)"
" Index Cond: (varcod = 76)"
pour info la table : t_donnees_fr1 (ident varchar,varcod=int ,valeur varchar)
l'index:varcod_t_donnees_fr1_idx
CREATE INDEX varcod_t_donnees_fr1_idx
ON t_donnees_fr1
USING btree
(varcod );
Hors ligne
Quelle est la version de postgresql, la configuration de la machine, et les valeurs de configuration du posgresql.conf, telles que work_mem, shared_buffer ?
Il faudrait plutôt le explain analyze de la requête qui donnerait plus d'information.
Postgresql s'attend à trouver 525331 lignes qui correspondent au critère (varcod=76).
Cela vous semble-t-il correspondre à la réalité ? (A priori oui d'après votre premier message)
Julien.
https://rjuju.github.io/
Hors ligne
bonsoir rjuju,
voici l'explain analyze
"Aggregate (cost=249076.18..249076.19 rows=1 width=2) (actual time=123309.785..123309.788 rows=1 loops=1)"
" -> Bitmap Heap Scan on t_donnees_fr1 (cost=9729.42..245180.77 rows=519388 width=2) (actual time=1449.767..120127.916 rows=516170 loops=1)"
" Recheck Cond: (varcod = 76)"
" -> Bitmap Index Scan on varcod_t_donnees_fr1_idx (cost=0.00..9599.57 rows=519388 width=0) (actual time=1257.186..1257.186 rows=516170 loops=1)"
" Index Cond: (varcod = 76)"
"Total runtime: 123364.180 ms"
ma version est la 9.1 je suis sur windows xp
shared_buffer= 1GB
work_mem=64MB
ce qui est étonnant c'est que pour une même config on a d'un coté 3s et de l'autre 120 s.
re bonsoir
Hors ligne
Combien de mémoire vive avez vous sur la machine ? Je suppose 4Go, vu que vous avez positionné le shared_buffers à 1Go.
Sous windows, il est généralement conseillé de ne pas dépasser les 300 à 512 Mo pour ce paramètre, sous peine d'avoir une baisse conséquente des performances.
J'ai déjà été confronté à ce problème et je pense qu'il faudrait tester avec un shared_buffers à 256MB (et essayer d'augmenter progressivement) pour voir les impacts sur votre requête.
Je vous conseille également de faire attention à votre work_mem qui est peut-être un peu haut, sauf s'il n'y a jamais plus d'une dizaine de connexions simultanées sur votre base.
Julien.
https://rjuju.github.io/
Hors ligne
Bonjour,
Pouvez-vous essayer l'explain avec la syntaxe suivante:
explain (analyze on, buffers on) ma_requête… sur les deux requêtes. Je ne suis pas persuadé quant à moi qu'un des deux cas doive forcément être plus efficace que l'autre, et ça ne m'étonnerait pas plus que ça qu'il y ait beaucoup plus de données à lire, de façon aléatoire, sur la seconde.
Marc.
Hors ligne
Que veux-tu dire par : "Je ne suis pas persuadé quant à moi qu'un des deux cas doive forcément être plus efficace que l'autre".
J'aurai pensé au contraire que comme le 2ème est une sorte de "meta-schéma" c'était forcément moins efficace. C'est le sens de la 2ème partie de ta phrase?
Hors ligne
Oui. C'est mon avis, c'est probablement plus lent. D'où l'explain avec les buffers, ce qui donnera le nombre de blocs lus dans les deux cas.
Marc.
Hors ligne
bonsoir,
j'ai fait un cluster de ma table " 3 colonnes" (on me l' a suggéré) et là ma requête s'est fait en 1seconde
je vous envoie comme demandé les explain (analyze on, buffers on)
sur ma table "3 colonnes"
"Aggregate (cost=248748.18..248748.19 rows=1 width=2) (actual time=2616.215..2616.217 rows=1 loops=1)"
" Buffers: shared hit=4705"
" -> Bitmap Heap Scan on t_donnees_fr1 (cost=9729.42..244852.77 rows=519388 width=2) (actual time=85.904..1155.135 rows=516170 loops=1)"
" Recheck Cond: (varcod = 76)"
" Buffers: shared hit=4705"
" -> Bitmap Index Scan on varcod_t_donnees_fr1_idx (cost=0.00..9599.57 rows=519388 width=0) (actual time=85.000..85.000 rows=516170 loops=1)"
" Index Cond: (varcod = 76)"
" Buffers: shared hit=1416"
"Total runtime: 2616.393 ms"
sur ma table 150 colonnes
"Aggregate (cost=25457.01..25457.02 rows=1 width=8) (actual time=2941.360..2941.362 rows=1 loops=1)"
" Buffers: shared hit=18970"
" -> Seq Scan on exploitations (cost=0.00..24159.61 rows=518961 width=8) (actual time=0.013..1123.156 rows=518961 loops=1)"
" Buffers: shared hit=18970"
"Total runtime: 2941.518 ms"
bonsoir
Hors ligne
Donc c'était bien ça. Si un cluster a réduit le temps d'exécution, c'est bien qu'il y avait énormément de buffers à consulter de façon aléatoire. Faire un cluster a permis de regrouper les données au même endroit… temporairement (tout update déplacera éventuellement les données). Ici, on voit que la version 3 colonnes lit grosso-modo 6000 buffers, de façon aléatoire (pas si aléatoire que ça, puisque pour le moment les données sont triées grâce à cluster, d'où les bonnes performances). La version 150 colonnes lit 19000 blocs, mais de façon séquentielle, donc bien moins «risquée» en termes d'entrées-sorties (lire 1 ou 1000 blocs séquentiellement, c'est presque le même coût).
De toutes façons, les méta-schéma, en termes de performance, sont habituellement mauvais (entre autres, comme dans votre cas, parce qu'ils éparpillent physiquement les données). Si vous avez vraiment besoin de stocker des données sans schéma dans PostgreSQL, préférez le contrib hstore http://docs.postgresql.fr/9.1/hstore.html . Évidemment, ça n'est plus portable vers une autre base de données, si vous partez vers hstore.
Marc.
Hors ligne
bonjour,
je vais effectivement abandonnner cette "solution".
mais j'ai encore une question: en quoi l'utilisation du contrib hstore est plus interessant?
merci et bonne journée
Hors ligne
Avec le contrib hstore, vous aurez moins d'enregistrement. Au lieu d'un enregistrement par entité/clé/valeur, vous en aurez un par entité, contenant toutes les clés/valeurs associées. C'est bien plus efficace en termes de stockage (moins volumineux), et à la récupération: il est rare qu'on ne récupère qu'une clé d'une entité à la fois… donc autant les avoir toutes au même endroit.
Marc.
Hors ligne