Discussion :

[Issiel]

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]

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]

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?

[punkoff]

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

[lola06]

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]

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]

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 ?

[argoet]

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

[Issiel]

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.