Optimisation de requête

**Issiel** · 09/01/2012, 11h41

Bonjour bonjour. Je bosse actuellement sur un projet php avec une très grosse base de données contenant quelques milliards d'enregistrement. Si je viens vous voir aujourd'hui c'est que je bosse sur une optimisation de requête. Celle-ci durait plus de deux minutes avant que je mette les mains dans le cambouis, elle dure désormais une vingtaine de seconde. Mais bon, comme vous vous en doutez, 20 secondes c'est encore beaucoup trop long et il me faut une autre solution. Je ne suis pas du tout un spécialiste en SQL mais je me débrouille assez bien en général. Cependant ici, étant donné le volume de données, j'aurai besoin d'autres avis. Voila où j'en suis arrivé :

Code :

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

1
2
3
4
5
6
7
  SELECT fbs_import.lien_champ__phrase._pere
       , count(*) as nb
    FROM fbs_import.lien_niveau__phrase
         INNER JOIN fbs_import.lien_champ__phrase
           on fbs_import.lien_niveau__phrase._fils = fbs_import.lien_champ__phrase._fils
   WHERE fbs_import.lien_champ__phrase._pere in (4,5,25,26,27,31,32,33,34,35,36,38,39,41,50,55,59,65,69,82,86,87,88,89,92,93,94,96,97,98,99,100,114,115,116,122,124,126,144,145,149,315,385,406,409,411,416,418,422,434,610,632,638,752,801,843,966,991,1000,1017,1018,1019,1020,1021,1022,1023,1024,1108,1433,1807,1994,2675,2676,3795,3807,3808,3895,3896,3897,3898,3899,3900,3901,3902,3903,4084,4937,5094,5106,5174,5175)
GROUP BY fbs_import.lien_champ__phrase._pere

Merci à ceux qui se pencheront sur ce problème et chercherons à le résoudre

Bonne journée.

**punkoff** · 09/01/2012, 11h48

Bonjour,

Manque le SGBD, un descriptif des tables (en particulier les relations entre vos 2 tables), les indexs en place et à priori le plan d'execution.

**Issiel** · 09/01/2012, 11h58

En effet. Donc on tourne sous postgre.

Ici nous avons donc un lien entre les tables lien_champ__phrase et lien_niveau__phrase qui sont liées par le champ _fils de lien_niveau__phrase et le champ _fils de lien_champ_phrase.

Après ceci est déjà visible dans la requête. Il n'y a pas d'index. Pour ce qui est du plan d'exécution, que voulez-vous dire?

**argoet** · 09/01/2012, 12h02

Vous pouvez essayer ceci (sans aucune garantie

)
l'idée sous-jacente est d'utiliser dans un premier temps l’accès à la table "LCP" (devrait être indexée sur "père" normalement) avant de faire la jointure sur LNP
=====================================================
PS : Comment générez vous le contenu du "in"
PS2 : quelles sont les index sur vos 2 tables

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT LCP._pere, count(1) AS nb                           
FROM fbs_import.lien_niveau__phrase LNP ,  fbs_import.lien_champ__phrase LCP 
Where LCP._pere IN (
                   4,5,25,26,27,31,32,33,34,35,36,38,39,41,50,55,59,65,69,82,
                   86,87,88,89,92,93,94,96,97,98,99,100,114,115,116,122,124,
                   126,144,145,149,315,385,406,409,411,416,418,422,434,610,632,638,
                   752,801,843,966,991, 1000,1017,1018,1019,1020,1021,1022,1023,1024,
                   1108,1433,1807,1994, 2675,2676,3795,3807,3808,3895,3896,3897,
                   3898,3899,3900,3901,3902,3903,4084,4937,5094,5106,5174,5175
                   )
And  LNP._fils = LCP._fils
GROUP BY LCP._pere

**punkoff** · 09/01/2012, 12h14

Envoyé par Issiel

En effet. Donc on tourne sous postgre.

Ici nous avons donc un lien entre les tables lien_champ__phrase et lien_niveau__phrase qui sont liées par le champ _fils de lien_niveau__phrase et le champ _fils de lien_champ_phrase.

Pardon je parlais plus d'un point de vue MCD.

Il n'y a pas d'index.

Oki dans ce cas, il faut au minima créer un index sur votre condition de jointure.
PostgreSQL ne crée pas par défaut un index sur les foreign key.

Si pas suffisant il faudrai créer un index (au choix selon les temps de réponses) sur la table lien_champ__phrase :
- _pere, _fils
- _fils, _pere

Pour ce qui est du plan d'exécution, que voulez-vous dire?

Le plan d'éxécution de la requête permet de voir ce que fait l'optimiseur lorsqu'il construit la requête, de voir si les stats de vos tables sont à jour, etc

PgAdmin dispose d'une version texte / graphique, sinon il faut faire :

Code :

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

1
2
 
explain analyze ma_requete

**Issiel** · 09/01/2012, 12h15

Merci de votre réponse et de votre intérêt.
Donc au niveau des indexes, rien de spécial. Une clé primaire sur un champ nommé "_id" sinon c'est réellement tout.
En fait le système mis en place fait que des tables nommées objet_nomobjet, sont liées par des tables lien_objet1__objet2 par les champs _pere et _fils.

Pour ce qui est du IN. Il est généré en php avec la fonction implode sur une requête précédente.

J'ai testé votre solution mais le temps est globalement identique, à une seconde près.

**lola06** · 09/01/2012, 12h15

Envoyé par Issiel

Pour ce qui est du plan d'exécution, que voulez-vous dire?

Le plan d'exécution va te permettre de voir ce qui te prend le plus de ressource dans ta requête SQL.

Code :

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

EXPLAIN TaRequête

Et tu nous postes le résultat.

Mais déjà avec un index tu es quasi sûr que ta requête sera plus rapide.

**Issiel** · 09/01/2012, 12h20

Le voila donc.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
HashAggregate  (cost=1591990.92..1591997.10 rows=495 width=8)
 
  ->  Hash Join  (cost=440192.16..1561728.30 rows=6052523 width=8)
 
        Hash Cond: (lien_champ__phrase._fils = lien_niveau__phrase._fils)
 
        ->  Bitmap Heap Scan on lien_champ__phrase  (cost=98982.94..983268.66 rows=6052523 width=16)
 
              Recheck Cond: (_pere = ANY ('{4,5,25,26,27,31,32,33,34,35,36,38,39,41,50,55,59,65,69,82,86,87,88,89,92,93,94,96,97,98,99,100,114,115,116,122,124,126,144,145,149,315,385,406,409,411,416,418,422,434,610,632,638,752,801,843,966,991,1000,1017,1018,1019,1020,1021,1022,1023,1024,1108,1433,1807,1994,2675,2676,3795,3807,3808,3895,3896,3897,3898,3899,3900,3901,3902,3903,4084,4937,5094,5106,5174,5175}'::bigint[]))
 
              ->  Bitmap Index Scan on idx_lien_champ__phrase__pere  (cost=0.00..97469.81 rows=6052523 width=0)
 
                    Index Cond: (_pere = ANY ('{4,5,25,26,27,31,32,33,34,35,36,38,39,41,50,55,59,65,69,82,86,87,88,89,92,93,94,96,97,98,99,100,114,115,116,122,124,126,144,145,149,315,385,406,409,411,416,418,422,434,610,632,638,752,801,843,966,991,1000,1017,1018,1019,1020,1021,1022,1023,1024,1108,1433,1807,1994,2675,2676,3795,3807,3808,3895,3896,3897,3898,3899,3900,3901,3902,3903,4084,4937,5094,5106,5174,5175}'::bigint[]))
 
        ->  Hash  (cost=196752.43..196752.43 rows=8804943 width=8)
 
              ->  Seq Scan on lien_niveau__phrase  (cost=0.00..196752.43 rows=8804943 width=8)

**punkoff** · 09/01/2012, 13h25

explain analyze sinon on ne voit pas ce qui ne pourrai pas aller ! (et ne virez pas l'indentation c'est important pour voir les étapes)

edit : il fait un table scan de la table lien_niveau__phrase.
Avez-vous mit un index sur lien_niveau__phrase._fils comme je le vous conseillais ?

**Issiel** · 09/01/2012, 13h33

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
QUERY PLAN
 
HashAggregate  (cost=1591990.92..1591997.10 rows=495 width=8) (actual time=20900.329..20900.347 rows=91 loops=1)
 
  ->  Hash Join  (cost=440192.16..1561728.30 rows=6052523 width=8) (actual time=7234.902..19308.790 rows=8799081 loops=1)
 
        Hash Cond: (lien_champ__phrase._fils = lien_niveau__phrase._fils)
 
        ->  Bitmap Heap Scan on lien_champ__phrase  (cost=98982.94..983268.66 rows=6052523 width=16) (actual time=2551.189..8178.639 rows=8831662 loops=1)
 
              Recheck Cond: (_pere = ANY ('{4,5,25,26,27,31,32,33,34,35,36,38,39,41,50,55,59,65,69,82,86,87,88,89,92,93,94,96,97,98,99,100,114,115,116,122,124,126,144,145,149,315,385,406,409,411,416,418,422,434,610,632,638,752,801,843,966,991,1000,1017,1018,1019,1020,1021,1022,1023,1024,1108,1433,1807,1994,2675,2676,3795,3807,3808,3895,3896,3897,3898,3899,3900,3901,3902,3903,4084,4937,5094,5106,5174,5175}'::bigint[]))
 
              ->  Bitmap Index Scan on idx_lien_champ__phrase__pere  (cost=0.00..97469.81 rows=6052523 width=0) (actual time=2548.434..2548.434 rows=8831663 loops=1)
 
                    Index Cond: (_pere = ANY ('{4,5,25,26,27,31,32,33,34,35,36,38,39,41,50,55,59,65,69,82,86,87,88,89,92,93,94,96,97,98,99,100,114,115,116,122,124,126,144,145,149,315,385,406,409,411,416,418,422,434,610,632,638,752,801,843,966,991,1000,1017,1018,1019,1020,1021,1022,1023,1024,1108,1433,1807,1994,2675,2676,3795,3807,3808,3895,3896,3897,3898,3899,3900,3901,3902,3903,4084,4937,5094,5106,5174,5175}'::bigint[]))
 
        ->  Hash  (cost=196752.43..196752.43 rows=8804943 width=8) (actual time=4410.270..4410.270 rows=8804905 loops=1)
 
              ->  Seq Scan on lien_niveau__phrase  (cost=0.00..196752.43 rows=8804943 width=8) (actual time=0.018..2012.535 rows=8804905 loops=1)
 
Total runtime: 20900.534 ms

**Issiel** · 09/01/2012, 13h44

Pas encore, je vais voir avec mon équipe et essayer ça.

**Issiel** · 09/01/2012, 14h08

Bon et bien je suis allé voir la structure de la base de données. Tous les champs de toutes les tables sont indexées. Je ne sais trop pourquoi. J'ai donc supprimé tous les index qui ne me paraissaient pas utiles dans mes deux tables et je n'ai gardé que les index sur _id, _fil et _pere dans ces deux tables. Cependant je tourne toujours à 20 secondes.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
HashAggregate  (cost=1591990.92..1591997.10 rows=495 width=8) (actual time=20698.751..20698.764 rows=91 loops=1)
 
  ->  Hash Join  (cost=440192.16..1561728.30 rows=6052523 width=8) (actual time=7025.342..19123.259 rows=8799081 loops=1)
 
        Hash Cond: (lien_champ__phrase._fils = lien_niveau__phrase._fils)
 
        ->  Bitmap Heap Scan on lien_champ__phrase  (cost=98982.94..983268.66 rows=6052523 width=16) (actual time=2356.378..8002.636 rows=8831662 loops=1)
 
              Recheck Cond: (_pere = ANY ('{4,5,25,26,27,31,32,33,34,35,36,38,39,41,50,55,59,65,69,82,86,87,88,89,92,93,94,96,97,98,99,100,114,115,116,122,124,126,144,145,149,315,385,406,409,411,416,418,422,434,610,632,638,752,801,843,966,991,1000,1017,1018,1019,1020,1021,1022,1023,1024,1108,1433,1807,1994,2675,2676,3795,3807,3808,3895,3896,3897,3898,3899,3900,3901,3902,3903,4084,4937,5094,5106,5174,5175}'::bigint[]))
 
              ->  Bitmap Index Scan on idx_lien_champ__phrase__pere  (cost=0.00..97469.81 rows=6052523 width=0) (actual time=2353.638..2353.638 rows=8831663 loops=1)
 
                    Index Cond: (_pere = ANY ('{4,5,25,26,27,31,32,33,34,35,36,38,39,41,50,55,59,65,69,82,86,87,88,89,92,93,94,96,97,98,99,100,114,115,116,122,124,126,144,145,149,315,385,406,409,411,416,418,422,434,610,632,638,752,801,843,966,991,1000,1017,1018,1019,1020,1021,1022,1023,1024,1108,1433,1807,1994,2675,2676,3795,3807,3808,3895,3896,3897,3898,3899,3900,3901,3902,3903,4084,4937,5094,5106,5174,5175}'::bigint[]))
 
        ->  Hash  (cost=196752.43..196752.43 rows=8804943 width=8) (actual time=4395.361..4395.361 rows=8804905 loops=1)
 
              ->  Seq Scan on lien_niveau__phrase  (cost=0.00..196752.43 rows=8804943 width=8) (actual time=0.018..2008.315 rows=8804905 loops=1)
 
Total runtime: 20698.950 ms

**punkoff** · 09/01/2012, 14h21

Y a combien de ligne dans votre table lien_niveau__phrase ?

Votre plan ne me semble pas "mauvais" compte tenu du travaille demandé (jointure ~~group by~~ sur 8 millions de lignes)

Là où il perd le plsu de temps c'est sur la jointure entre vos deux tables, s'il n'utilise pas l'index présent c'est qu'il estime que ca sera encore plus long.

Vous pouvez peut-être vérifier ceci en faisant :

Code :

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

1
2
 
set enable_seqscan = false;

puis refaire un explain analyze de votre requête avec cette même session

**Issiel** · 09/01/2012, 14h25

8 804 781 lignes dans cette table.

**punkoff** · 09/01/2012, 14h31

ok, il n'utilisera jamais votre index dans ces conditions vu qu'il sélectionne presque toute les lignes de la table lien_niveau__phrase pour satisfaire votre requête.

Pour moi votre plan est donc correct.

D'autre personne auront peut-être un avis différent.

**Issiel** · 09/01/2012, 14h40

D'accord merci pour l'aide apportée. J'ai remodifié un peu les index et j'ai fini par descendre à un temps d'exécution d'après php d'environ 10 secondes ce qui n'est déjà pas si mal. Après je pense que c'est encore trop long donc je vais essayer de voir avec le chef si on ne peut pas passer cette étape qui n'est pas forcément vitale. Je continuerai de jeter un oeil sur ce sujet au cas où quelqu'un voit autre chose.

Merci encore.

**Waldar** · 09/01/2012, 14h45

Sans garantie, vous pouvez essayer celle-ci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
With nvp AS
(
  SELECT _fils, count(*) as nb
    FROM fbs_import.lien_niveau__phrase
GROUP BY _fils
)
  SELECT chp._pere
       , sum(nvp.nb) as nb
    FROM fbs_import.lien_champ__phrase as chp
         INNER JOIN nvp
           ON nvp._fils = chp._fils
   WHERE chp._pere in (4,5,25,26,27,31,32,33,34,35,36,38,39,41,50,55,59,65,69,82,86,87,88,89,92,93,94,96,97,98,99,100,114,115,116,122,124,126,144,145,149,315,385,406,409,411,416,418,422,434,610,632,638,752,801,843,966,991,1000,1017,1018,1019,1020,1021,1022,1023,1024,1108,1433,1807,1994,2675,2676,3795,3807,3808,3895,3896,3897,3898,3899,3900,3901,3902,3903,4084,4937,5094,5106,5174,5175)
GROUP BY chp._pere