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
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
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]
Partager