Forums PostgreSQL.fr

xavier-Pierre

Membre

table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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?

rjuju

Administrateur

Re : table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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/

xavier-Pierre

Membre

Re : table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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

rjuju

Administrateur

Re : table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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/

xavier-Pierre

Membre

Re : table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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

rjuju

Administrateur

Re : table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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/

Marc Cousin

Membre

Re : table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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.

flo

Membre

Re : table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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?

Marc Cousin

Membre

Re : table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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.

xavier-Pierre

Membre

Re : table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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

Marc Cousin

Membre

Re : table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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.

xavier-Pierre

Membre

Re : table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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

Marc Cousin

Membre

Re : table nombreux champs peu d' enr vs table peu de champs nombreux enr.

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.