Discussion :

[xavier-Pierre]

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:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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?

[estofilo]

je pensais q'un sgbd était optimisé pour des tables avec peu de colonnes même avec beaucoup d'enregistrements

En règle générale, non pas du tout. Notamment, ça prend beaucoup plus de place pour stocker la même chose.
Comparer les deux avec la fonction pg_total_relation_size() pour se faire une idée de la différence.

Sur la requête citée, le cas où ça pourrait être gagnant serait pour les champs dont la valeurs serait vide la plupart du temps et non recopiées dans le nouveau modèle.

Poste un EXPLAIN ANALYZE des 2 requêtes pour avoir éventuellement une analyse plus fine.

[xavier-Pierre]

bonsoir estofilo,
j'ai testé la fonction pg_total_relation_size, il n' y a pas photo de l'ordre de 1 à 13. mais même dans ce cas ,je n'ai pas une table monstrueusement grande,alors 120s pour une simple requete cela me parait enorme
je te donne les 2 explain analyze
pour la "table 3 colonnes"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
"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"

pour la table originelle 150 colonnes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
"Aggregate  (cost=25380.01..25380.02 rows=1 width=8) (actual time=7872.318..7872.320 rows=1 loops=1)"
"  ->  Seq Scan on exploitations  (cost=0.00..24082.61 rows=518961 width=8) (actual time=0.078..6007.967 rows=518961 loops=1)"
"Total runtime: 7872.707 ms"

bonsoir

[estofilo]

Il faudrait regarder ce que fait le système pendant ces 120s mais certainement qu'il attend le disque l'essentiel du temps. Le disque doit faire des accès non séquentiels, c'est ce qu'il y a de plus lent.
A partir de là il y a plusieurs pistes (en-dehors d'abandonner cette structure de données défavorable):
1) faire un CLUSTER de la table sur l'index varcod_t_donnees_fr1_idx.
ca va réécrire toute la table en rangeant ensemble les données de même valeur par rapport à cet index
OU/ET
2) augmenter considérablement random_page_cost (4.0 par défaut), moi je le monterai par incréments de 5 jusqu'à ce que l'optimiseur décide de changer de plan (ça devrait faire augmenter le coût du Bitmap Heap Scan).

Mais dans tous les cas je présume que ça va rester loin des perfs de la requête 1.

[xavier-Pierre]

bonsoir estofilo,
j'ai comme tu l'as suggéré fait un cluster sur l'index et là... ma requête se fait en 1 seconde ,plus rapide que la requête 1: je dis bravo
si je fais un cluster sur un index portant sur deux champs ,est ce que cela peut être jouable?
bonsoir et merci

[estofilo]

Le CLUSTER porte forcément sur un seul index.
Il y a aussi un autre inconvénient à considérer, c'est qu'il faut le refaire régulièrement si le contenu de la table est souvent mis à jour. Pour les tables dans lesquelles on écrit rarement, c'est bien.

Edit: l'index peut porter sur 2 champs, mais je ne sais pas si ça sert à quelque chose ici.