Table nombreux champs peu d'enregistrements vs table peu de champs nombreux enregistrements

[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 :

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 :

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]

Envoyé par xavier-Pierre

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 :

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 :

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.

[ymoreau]

Pour les requêtes où l'on veut lire toutes les valeurs d'une colonne (selon l'application dans sa globalité, pas juste pour un cas particulier). Il existe aussi des SGBD qui sont organisés par colonne et pas par ligne. C'est à dire que lire toutes les valeurs d'un champ sera plus rapide, mais lire tous les champs d'une ligne sera moins rapide.

Sinon comme l'a dit estofilo je pense qu'une table à 3 colonnes comme tu l'as fait n'est utile que s'il y a beaucoup de valeurs NULL.

[Waldar]

Ce genre de modélisation, on a été nombreux à l'essayer, et nombreux à vite s'en éloigner.
Appétissante de loin, des requêtes simples deviennent infernales à écrire.

Écrivez la requête qui ramène l'ensemble des id, col1, col2, col3, col4, col5 qui répondent à ces critères :
Col2 dans ('A', 'B', 'C') ET (Col3 = 4 OU Col5 = 5)

Dans le premier cas, c'est simple :

Code :

1
2
3
4
SELECT id, col1, col2, col3, col4, col5
  FROM MaTable
 WHERE col2 IN ('A', 'B', 'C')
   AND (Col3 = 4 OR Col5 = 5);

Je vous laisse le soin d'écrire la même requête avec votre table transformée (ma version fait 25 lignes).

[xavier-Pierre]

bonjour,
je crois en effet que je vais abandonné cette "solution", mais soyons positif cela m'a permis d’appréhender certains critères d'optimisation
merci à tous et bonne journée

[Waldar]

Histoire de ne pas perdre ce que j'ai écrit voici le jeu de test que je m'étais construit :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE table_R
AS
SELECT 1 AS id, 'a' AS c1, 'A' AS c2, 1 AS c3, 4 AS c4, 5 AS c5 union ALL
SELECT 2      , 'b'      , NULL     , 2      , 5      , 5       union ALL
SELECT 3      , 'c'      , 'C'      , 3      , 6      , 5;
 
CREATE TABLE table_T
AS
SELECT id, 'c1' AS nom_col, c1 AS valeur        FROM table_R union ALL
SELECT id, 'c2'           , c2                  FROM table_R union ALL
SELECT id, 'c3'           , cast(c3 AS varchar) FROM table_R union ALL
SELECT id, 'c4'           , cast(c4 AS varchar) FROM table_R union ALL
SELECT id, 'c5'           , cast(c5 AS varchar) FROM table_R;

La première requête donc :

Code :

1
2
3
4
5
  SELECT id, c1, c2, c3, c4, c5
    FROM table_R
   WHERE c2 IN ('A', 'B', 'C')
     AND (c4 = 4 OR c5 = 5)
ORDER BY id ASC;

La requête sur la table transformée :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
  SELECT id
       , max(case nom_col when 'c1' then valeur end) AS c1
       , max(case nom_col when 'c2' then valeur end) AS c2
       , cast(max(case nom_col when 'c3' then valeur end) AS integer) AS c3
       , cast(max(case nom_col when 'c4' then valeur end) AS integer) AS c4
       , cast(max(case nom_col when 'c5' then valeur end) AS integer) AS c5
    FROM table_T
   WHERE nom_col IN ('c1', 'c2', 'c3', 'c4', 'c5')
     AND id IN (SELECT id
                  FROM table_T
                 WHERE nom_col = 'c2'
                   AND valeur IN ('A', 'B', 'C')
                 intersect -- AND
                (
                SELECT id
                  FROM table_T
                 WHERE nom_col = 'c4'
                   AND valeur = cast(4 AS varchar)
                 union ALL -- OR
                SELECT id
                  FROM table_T
                 WHERE nom_col = 'c5'
                   AND valeur = cast(5 AS varchar)
                )
               )
GROUP BY id
ORDER BY id ASC;

Si quelqu'un a d'autres idées pour écrire la seconde requête, ça serait intéressant !

Mais en effet à moins d'un besoin extrêmement spécifique il vaut mieux envisager de rester sur une modélisation classique.

Je débute en PostgreSQL, sait-il gérer le partitionnement vertical ?
Ça pourrait être une solution à votre problème.

[punkoff]

Envoyé par Waldar

Si quelqu'un a d'autres idées pour écrire la seconde requête, ça serait intéressant !

Bonjour Waldar,

En passant par un group by + having on peut s'en sortir avec moins de table scan, c'est à approfondir mais ... :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SELECT id
       , max(case nom_col when 'c1' then valeur end) AS c1
       , max(case nom_col when 'c2' then valeur end) AS c2
       , cast(max(case nom_col when 'c3' then valeur end) AS integer) AS c3
       , cast(max(case nom_col when 'c4' then valeur end) AS integer) AS c4
       , cast(max(case nom_col when 'c5' then valeur end) AS integer) AS c5
    FROM table_T a
WHERE nom_col IN ('c1', 'c2', 'c3', 'c4', 'c5') AND a.id IN (
  SELECT b.id 
  FROM table_t b 
  WHERE (nom_col = 'c2' AND valeur IN ('A', 'B', 'C'))
   OR (nom_col = 'c4' AND valeur = cast(4 AS varchar)) 
   OR (nom_col = 'c5' AND valeur = cast(5 AS varchar))
  GROUP BY id
  --having sum(case when nom_col = 'c2' then 1 when nom_col = 'c4' or nom_col = 'c5' then 100 end) > 100)
  HAVING sum(case when nom_col = 'c2' then 1 else 0 end) > 0 AND sum(case when nom_col = 'c4' OR nom_col = 'c5' then 1 else 0 end) > 0)
GROUP BY id;

[Waldar]

Très bien !

[SQLpro]

Le problème de PostGreSQL est double :
1) il ne sait pas faire des scans d'index
2) il n'implémente pas les vues indexées.
Ces deux éléments auraient été plus qu’appréciable dans votre cas, car l'optimisation qui en résulterait serait au plus la lecture de quelques pages...

A +

[estofilo]

Envoyé par SQLpro

Le problème de PostGreSQL est double :
1) il ne sait pas faire des scans d'index
2) il n'implémente pas les vues indexées.
Ces deux éléments auraient été plus qu’appréciable dans votre cas, car l'optimisation qui en résulterait serait au plus la lecture de quelques pages...

Mais de quel problème on parle précisément?
Si ça se limite au SELECT sum(champn) FROM table_a_150_champs qui est trop lent, on peut très bien imaginer de maintenir une table à une seule ligne et 150 colonnes avec les SUM(champs_n) précalculés dans chaque colonne et mis à jour par trigger. L'accès en lecture serait instantané et le temps passé à mettre à jour cette table serait lissé sur tous les insert/update. Cette technique est efficace pour des fonctions simples comme SUM ou COUNT.

Mais si les remarques ci-dessus portent sur la question plus générale du modèle EAV, d'abord postgres fait des scans d'index, donc le point 1) n'a pas lieu d'être, et sur le point 2) il faudrait déjà préciser ce que des vues peuvent apporter au problème, car on ne peut pas dire que ce soit notoirement évoqué dans les études de ce modèle.