Discussion :

[jmtiger]

Bonjour,


Je rencontre le problème suivant la requête (Test n°1 ) s'exécute en 39 secondes
pour une période allant du 01/12/2014 au 22/12/2014 alors que Test n°2 ne prend
que 148 milliseconde pour une période allant du 01/01/2014 au 22/12/2014.
J'ai modifié les périodes et le problème n’apparaît que sur décembre 2014.
J'ai rejoué les requêtes avec Explain et j'ai constaté que PG n'utilise pas
la même stratégie (index de recherche différent).
Peut-on modifier ce comportement et si oui comment ?
La table vente contient 1480307 enregistrements et la table lignevente 5832585.
PostgreSQL 8.4.10

Merci




Test n° 1

Code :

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

1
2
3
4
5
6
select to_char(datevente,'YYYY') as an,to_char(datevente,'MM') as mois,sum(quantite) as qv 
from vente T1 inner join lignevente T2 on T2.serialvente=T1.serial  
where T1.datevente >='2014-12-01' and T1.datevente <='2014-12-22'
and T2.serialarticle=33862
and T2.typearticle='1' and T1.etat='V' 
group by 1,2 order by 1,2

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] 

 [Executed: 22/12/14 14:32:58 CET ] [Execution: 39789/ms] 


 QUERY PLAN                                                                                                                                           
 ---------------------------------------------------------------------------------------------------------------------------------------------------- 
 GroupAggregate  (cost=31.20..31.24 rows=1 width=8)                                                                                                   
   ->  Sort  (cost=31.20..31.21 rows=1 width=8)                                                                                                       
         Sort Key: (to_char((t1.datevente)::timestamp with time zone, 'YYYY'::text)), (to_char((t1.datevente)::timestamp with time zone, 'MM'::text)) 
         ->  Nested Loop  (cost=18.36..31.19 rows=1 width=8)                                                                                          
               ->  Index Scan using vente_idx1 on vente t1  (cost=0.00..8.79 rows=1 width=8)                                                          
                     Index Cond: ((datevente >= '2014-12-01'::date) AND (datevente <= '2014-12-22'::date))                                            
                     Filter: ((etat)::text = 'V'::text)                                                                                               
               ->  Bitmap Heap Scan on lignevente t2  (cost=18.36..22.38 rows=1 width=8)                                                              
                     Recheck Cond: ((t2.serialvente = t1.serial) AND (t2.serialarticle = 33862))                                                      
                     Filter: (t2.typearticle = '1'::bpchar)                                                                                           
                     ->  BitmapAnd  (cost=18.36..18.36 rows=1 width=0)                                                                                
                           ->  Bitmap Index Scan on lignevente_idx0  (cost=0.00..5.56 rows=36 width=0)                                                
                                 Index Cond: (t2.serialvente = t1.serial)                                                                             
                           ->  Bitmap Index Scan on lignevente_idx2  (cost=0.00..12.45 rows=413 width=0)                                              
                                 Index Cond: (t2.serialarticle = 33862)                                                                               

 15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] 

 [Executed: 22/12/14 14:27:17 CET ] [Execution: 78/ms] 

Test n° 2

Code :

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

1
2
3
4
5
6
select to_char(datevente,'YYYY') as an,to_char(datevente,'MM') as mois,sum(quantite) as qv 
from vente T1 inner join lignevente T2 on T2.serialvente=T1.serial  
where T1.datevente >='2014-01-01' and T1.datevente <='2014-12-22'
and T2.serialarticle=33862
and T2.typearticle='1' and T1.etat='V' 
group by 1,2 order by 1,2

12 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] 

 [Executed: 22/12/14 14:32:21 CET ] [Execution: 148/ms] 


 QUERY PLAN                                                                                                                                           
 ---------------------------------------------------------------------------------------------------------------------------------------------------- 
 GroupAggregate  (cost=5243.13..5248.79 rows=174 width=8)                                                                                             
   ->  Sort  (cost=5243.13..5243.57 rows=174 width=8)                                                                                                 
         Sort Key: (to_char((t1.datevente)::timestamp with time zone, 'YYYY'::text)), (to_char((t1.datevente)::timestamp with time zone, 'MM'::text)) 
         ->  Nested Loop  (cost=0.00..5236.66 rows=174 width=8)                                                                                       
               ->  Index Scan using lignevente_idx2 on lignevente t2  (cost=0.00..1654.33 rows=413 width=8)                                           
                     Index Cond: (serialarticle = 33862)                                                                                              
                     Filter: (typearticle = '1'::bpchar)                                                                                              
               ->  Index Scan using vente_idx0 on vente t1  (cost=0.00..8.66 rows=1 width=8)                                                          
                     Index Cond: (t1.serial = t2.serialvente)                                                                                         
                     Filter: ((t1.datevente >= '2014-01-01'::date) AND (t1.datevente <= '2014-12-22'::date) AND ((t1.etat)::text = 'V'::text))        

 10 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] 

[Executed: 22/12/14 14:30:06 CET ] [Execution: 65/ms]

[SQLpro]

En fait le comportement est normal mais PostGreSQL possède de nombreuses limitations :
1) un optimiseur peu pointu
2) l'absence de possibilité de placer des tags de requête (hint).

Sur la normalité de ce que fait PG :

• l'optimiseur calcule la meilleure stratégie de jointure en comparant le coût théorique d'une recherche d'index suivi d'une jointure d'index à table, par rapport à un balayage complet de la table. Si vos statistiques sont à jour, il devrait trouver la meilleure stratégie et vous seriez toujours gagnant... Cependant l'optimiseur de PG possède de nombreuses limitations :
  --
• son niveau de calcul et d'utilisation des stats est faible par rapport à SQL Server ou Oracle
  --
• dès que le nombre de jointure est important dans une requête il passe à des stratégies plus vagues (GEKO) qui produisent des plans plus hasardeux car sinon le temps d'explorer tous les cas de figure serait trop long.

Dans un tel cas, sur n'importe quel autre SGBD Relationnel (SQL Server, Oracle....) dont le plan serait aussi mauvais (cela arrive rarement car les optimiseurs d'Oracle et plus encore de SQL Server sont très pointus...) il serait alors possible de placer des tags dans les requêtes pour forcer l'écriture du plan. Par exemple d'interdire la prise d'index et de faire uniquement du scan de table dans tous les cas (ou l'inverse).
Malheureusement cela est impossible dans PostGreSQL, car les concepteurs ont interdit ce principe arguant que les utilisateurs n'en aurait pas besoin. C'est un point de discorde ancien et notable qui fait que PG n'est pas apte à traiter de forts volumes de données de manière performantes...
Notez que vous pouvez opter pour la version payante de PostGreSQL (Enterprise DB) qui elle intègre ce genre de chose (et là je me marre car ceux qui voulait du libre se retrouvent à payer fort cher et avoir au final un SGBDR notablement moins bon que SQL Server ou Oracle !!!)

Il vous reste néanmoins quelques points de tuning :

• augmenter le seuil de déclenchement de GEKO et plus généralement modifier quelques un des paramètres de l'optimiseur... Mais cette solution étant globale, vous risquez plus que vous ne gagneriez !
• recalculer plus régulièrement les stats avec un échantillon plus profond, voir 100% et plus d'entrées dans l'histogramme
• revoir le conception de vos index en faisant des index couvrants et en mettant les colonnes les plus sélectives en premier (là il vaudrait nettement mieux être sur SQL Server et utiliser la clause INCLUDE !!!)
• récrire vos requêtes différemment (par exemple avec du EXISTS, du IN...) pour voir si un nouveau plus plus affiné en sort
• revoir la conception de vos table (refactoring) pour en soulager la cardinalité et le degré
• changer de SGBDR !

A +

PS : pour vous aider un peu plus, postez l'intégralité du DDL de vos tables et index en jeu.

[Waldar]

Pour faire plus court que SQLPro, passez un coup de stats sur vos tables :

Code :

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

1
2
analyze vente;
analyze lignevente;

[ced]

Peut-être aussi serait-il temps, si vous le pouvez, de monter en version votre serveur PostgreSQL... La version 8.4 commence à dater et de nombreuses améliorations ont été apportées au planificateur de requêtes depuis.
Mais pour cela, encore faut-il que vous ayez la main sur le serveur.