I have attached the requested information.

You will see that the query is quite messy and could be easily improved.
Unfortunately, it came from a third party application and we do not have 
access to the source code.

Thanks for your help,

Best Regards,
Vincent




Michael Fuhr wrote:
> On Tue, Mar 13, 2007 at 09:19:47AM +0100, [EMAIL PROTECTED] wrote:
>   
>> Is there an option in the 8.2.3 to change in order to have the same 
>> execution plan than before ?
>>     
>
> Let's see if we can figure out why 8.2.3 is choosing a bad plan.
> Have you run ANALYZE on the tables in 8.2.3?  Could you post the
> query and the complete output of EXPLAIN ANALYZE (preferably without
> wrapping) for both versions?
>
>   


Ce message et toutes les pièces jointes sont établis à l'attention exclusive de 
leurs destinataires et sont confidentiels. Si vous recevez ce message par 
erreur, merci de le détruire et d'en avertir immédiatement l'expéditeur. 
L'internet ne permettant pas d'assurer l'intégrité de ce message, le contenu de 
ce message ne représente en aucun cas un engagement de la part de Leroy Merlin.

-bash-2.05b$ psql CCM 
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

CCM=# VACUUM FULL ANALYZE ;
VACUUM
CCM=# explain ANALYZE SELECT distinct C.cod_couleur_panneau, 
C.cod_couleur_panneau, cast ('LM05' as varchar), cast ('OMM_TEINTE' as 
varchar), cast ('IM' as varchar) FROM lm05_t_modele AS A, 
lm05_t_couleur_panneau AS C, lm05_t_infos_modele AS D, lm05_t_tarif_panneau AS 
G , lm05_t_composition AS E , lm05_t_couleur_profile AS F , 
cm_gestion_modele_ca as H, mag_gestion_modele_mag as I WHERE 
A.cod_type_ouverture = 'OUV_COU' AND A.cod_type_panneau = 'PAN_MEL' AND 
A.cod_modele = C.cod_modele AND A.cod_modele = D.cod_modele AND A.cod_modele = 
G.cod_modele AND G.cod_tarif_panneau = C.cod_tarif_panneau AND A.cod_modele = 
E.cod_modele AND nb_vantaux >= 2 AND A.cod_modele = F.cod_modele AND 
F.couleur_profile = 'acajou mat' AND F.cod_tarif_profile = G.cod_tarif_profile 
AND A.cod_fournisseur = '5132' AND A.cod_gamme_prof = 'Design Xtra' AND 
C.ht_min < 2000 AND C.ht_max >= 2000 AND D.largeur_maxi_rail >= 1000 AND 
C.cod_aspect = 'tons bois et cuirs' AND C.cod_gamme_panneau = 'BOIS et CUIR 
XTRA 3' AND ((G.lrg_min < 1000 AND G.lrg_max >= 1000) OR (G.lrg_min < 500 AND 
G.lrg_max >= 500) OR (G.lrg_min < 333.333333333333 AND G.lrg_max >= 
333.333333333333) OR (G.lrg_min < 250 AND G.lrg_max >= 250) OR (G.lrg_min < 200 
AND G.lrg_max >= 200) OR (G.lrg_min < 166.666666666667 AND G.lrg_max >= 
166.666666666667) OR (G.lrg_min < 142.857142857143 AND G.lrg_max >= 
142.857142857143) OR (G.lrg_min < 125 AND G.lrg_max >= 125) OR (G.lrg_min < 
111.111111111111 AND G.lrg_max >= 111.111111111111) OR (G.lrg_min < 100 AND 
G.lrg_max >= 100)) AND H.idmagasin = '011' AND H.idoav='PC_PLACARD' AND 
H.cod_modele = A.cod_modele AND H.autorise = 1 AND I.idmagasin = '011' AND 
I.idoav='PC_PLACARD' AND I.cod_modele = A.cod_modele AND I.selection = 1;
                                                                                
                                          
                                                                                
                                          
                                                                                
          QUERY PLAN                      
                                                                                
                                          
                                                                                
                                          
                                                                    
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
 Unique  (cost=5276.93..5276.95 rows=1 width=32) (actual 
time=28977.716..28980.225 rows=140 loops=1)
   ->  Sort  (cost=5276.93..5276.94 rows=1 width=32) (actual 
time=28977.712..28978.464 rows=1400 loops=1)
         Sort Key: c.cod_couleur_panneau, c.cod_couleur_panneau, 
'LM05'::character varying, 'OMM_TEINTE'::character varying, 'IM'::character 
varying
         ->  Nested Loop  (cost=6.31..5276.92 rows=1 width=32) (actual 
time=111.982..28945.376 rows=1400 loops=1)
               Join Filter: (a.cod_modele = d.cod_modele)
               ->  Nested Loop  (cost=6.31..5268.80 rows=1 width=60) (actual 
time=111.790..28626.234 rows=1400 loops=1)
                     Join Filter: (a.cod_modele = e.cod_modele)
                     ->  Nested Loop  (cost=6.31..5246.81 rows=1 width=56) 
(actual time=111.026..28406.507 rows=280 loops=1)
                           Join Filter: ((a.cod_modele = f.cod_modele) AND 
((f.cod_tarif_profile)::text = (g.cod_tarif_profile)::text))
                           ->  Nested Loop  (cost=6.31..5147.63 rows=1 
width=70) (actual time=94.114..27028.607 rows=1120 loops=1)
                                 Join Filter: (h.cod_modele = a.cod_modele)
                                 ->  Nested Loop  (cost=6.31..4634.34 rows=1 
width=66) (actual time=87.369..20023.134 rows=1120 loops=1)
                                       Join Filter: (i.cod_modele = 
a.cod_modele)
                                       ->  Nested Loop  (cost=6.31..4623.51 
rows=1 width=62) (actual time=87.018..19664.375 rows=1120 loops=1)
                                             Join Filter: ((a.cod_modele = 
c.cod_modele) AND ((g.cod_tarif_panneau)::text = (c.cod_tarif_panneau)::text))
                                             ->  Seq Scan on 
lm05_t_couleur_panneau c  (cost=0.00..1565.60 rows=1 width=62) (actual 
time=23.551..28.649 rows=280 loops=1)
                                                   Filter: ((ht_min < 2000) AND 
(ht_max >= 2000) AND ((cod_aspect)::text = 'tons bois et cuirs'::text) AND 
((cod_gamme_panneau)::text = 'BOIS et CUIR XTRA 3'::text))
                                             ->  Hash Join  (cost=6.31..3056.17 
rows=116 width=47) (actual time=60.055..70.078 rows=48 loops=280)
                                                   Hash Cond: (g.cod_modele = 
a.cod_modele)
                                                   ->  Seq Scan on 
lm05_t_tarif_panneau g  (cost=0.00..2977.08 rows=19097 width=43) (actual 
time=0.008..67.670 rows=4062 loops=280)
                                                         Filter: (((lrg_min < 
1000) AND (lrg_max >= 1000)) OR ((lrg_min < 500) AND (lrg_max >= 500)) OR 
(((lrg_min)::numeric < 333.333333333333) AND ((lrg_max)::numeric >= 
333.333333333333)) OR ((lrg_min < 250) AND (lrg_max >= 250)) OR ((lrg_min < 
200) AND (lrg_max >= 200)) OR (((lrg_min)::numeric < 166.666666666667) AND 
((lrg_max)::numeric >= 166.666666666667)) OR (((lrg_min)::numeric < 
142.857142857143) AND ((lrg_max)::numeric >= 142.857142857143)) OR ((lrg_min < 
125) AND (lrg_max >= 125)) OR (((lrg_min)::numeric < 111.111111111111) AND 
((lrg_max)::numeric >= 111.111111111111)) OR ((lrg_min < 100) AND (lrg_max >= 
100)))
                                                   ->  Hash  (cost=6.30..6.30 
rows=1 width=4) (actual time=0.114..0.114 rows=1 loops=1)
                                                         ->  Seq Scan on 
lm05_t_modele a  (cost=0.00..6.30 rows=1 width=4) (actual time=0.040..0.109 
rows=1 loops=1)
                                                               Filter: 
(((cod_type_ouverture)::text = 'OUV_COU'::text) AND ((cod_type_panneau)::text = 
'PAN_MEL'::text) AND (cod_fournisseur = 5132) AND ((cod_gamme_prof)::text = 
'Design Xtra'::text))
                                       ->  Seq Scan on mag_gestion_modele_mag i 
 (cost=0.00..8.78 rows=165 width=4) (actual time=0.058..0.220 rows=165 
loops=1120)
                                             Filter: (((idmagasin)::text = 
'011'::text) AND ((idoav)::text = 'PC_PLACARD'::text) AND (selection = 1))
                                 ->  Seq Scan on cm_gestion_modele_ca h  
(cost=0.00..511.27 rows=161 width=4) (actual time=0.030..6.152 rows=165 
loops=1120)
                                       Filter: (((idmagasin)::text = 
'011'::text) AND ((idoav)::text = 'PC_PLACARD'::text) AND (autorise = 1))
                           ->  Seq Scan on lm05_t_couleur_profile f  
(cost=0.00..98.86 rows=21 width=22) (actual time=0.849..1.205 rows=32 
loops=1120)
                                 Filter: ((couleur_profile)::text = 'acajou 
mat'::text)
                     ->  Seq Scan on lm05_t_composition e  (cost=0.00..14.82 
rows=573 width=4) (actual time=0.010..0.465 rows=573 loops=280)
                           Filter: (nb_vantaux >= 2)
               ->  Seq Scan on lm05_t_infos_modele d  (cost=0.00..6.06 rows=165 
width=4) (actual time=0.004..0.136 rows=165 loops=1400)
                     Filter: (largeur_maxi_rail >= 1000)
 Total runtime: 28980.630 ms
(35 rows)

CCM=# 

-bash-2.05b$ psql CCM
Welcome to psql 7.3.10-RH, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

CCM=# EXPLAIN ANALYZE SELECT distinct C.cod_couleur_panneau, 
C.cod_couleur_panneau, cast ('LM05' as varchar), cast ('OMM_TEINTE' as 
varchar), cast ('IM' as varchar) FROM lm05_t_modele AS A, 
lm05_t_couleur_panneau AS C, lm05_t_infos_modele AS D, lm05_t_tarif_panneau AS 
G , lm05_t_composition AS E , lm05_t_couleur_profile AS F , 
cm_gestion_modele_ca as H, mag_gestion_modele_mag as I WHERE 
A.cod_type_ouverture = 'OUV_COU' AND A.cod_type_panneau = 'PAN_MEL' AND 
A.cod_modele = C.cod_modele AND A.cod_modele = D.cod_modele AND A.cod_modele = 
G.cod_modele AND G.cod_tarif_panneau = C.cod_tarif_panneau AND A.cod_modele = 
E.cod_modele AND nb_vantaux >= 2 AND A.cod_modele = F.cod_modele AND 
F.couleur_profile = 'acajou mat' AND F.cod_tarif_profile = G.cod_tarif_profile 
AND A.cod_fournisseur = '5132' AND A.cod_gamme_prof = 'Design Xtra' AND 
C.ht_min < 2000 AND C.ht_max >= 2000 AND D.largeur_maxi_rail >= 1000 AND 
C.cod_aspect = 'tons bois et cuirs' AND C.cod_gamme_panneau = 'BOIS et CUIR 
XTRA 3' AND ((G.lrg_min < 1000 AND G.lrg_max >= 1000) OR (G.lrg_min < 500 AND 
G.lrg_max >= 500) OR (G.lrg_min < 333.333333333333 AND G.lrg_max >= 
333.333333333333) OR (G.lrg_min < 250 AND G.lrg_max >= 250) OR (G.lrg_min < 200 
AND G.lrg_max >= 200) OR (G.lrg_min < 166.666666666667 AND G.lrg_max >= 
166.666666666667) OR (G.lrg_min < 142.857142857143 AND G.lrg_max >= 
142.857142857143) OR (G.lrg_min < 125 AND G.lrg_max >= 125) OR (G.lrg_min < 
111.111111111111 AND G.lrg_max >= 111.111111111111) OR (G.lrg_min < 100 AND 
G.lrg_max >= 100)) AND H.idmagasin = '011' AND H.idoav='PC_PLACARD' AND 
H.cod_modele = A.cod_modele AND H.autorise = 1 AND I.idmagasin = '011' AND 
I.idoav='PC_PLACARD' AND I.cod_modele = A.cod_modele AND I.selection = 1;
                                                                                
                                                                                
                                                                                
                                                                                
              QUERY PLAN                                                        
                                                                                
                                                                                
                                                                                
                                      
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------
 Unique  (cost=232.48..232.51 rows=1 width=497) (actual time=821.34..839.75 
rows=140 loops=1)
   ->  Sort  (cost=232.48..232.49 rows=2 width=497) (actual time=821.34..822.06 
rows=1400 loops=1)
         Sort Key: c.cod_couleur_panneau, c.cod_couleur_panneau, 
'LM05'::character varying, 'OMM_TEINTE'::character varying, 'IM'::character 
varying
         ->  Hash Join  (cost=208.29..232.47 rows=2 width=497) (actual 
time=777.53..780.99 rows=1400 loops=1)
               Hash Cond: ("outer".cod_modele = "inner".cod_modele)
               ->  Seq Scan on lm05_t_infos_modele d  (cost=0.00..22.50 
rows=333 width=4) (actual time=3.93..4.46 rows=165 loops=1)
                     Filter: (largeur_maxi_rail >= 1000)
               ->  Hash  (cost=208.28..208.28 rows=1 width=493) (actual 
time=772.34..772.34 rows=0 loops=1)
                     ->  Hash Join  (cost=184.09..208.28 rows=1 width=493) 
(actual time=582.72..770.73 rows=1400 loops=1)
                           Hash Cond: ("outer".cod_tarif_profile = 
"inner".cod_tarif_profile)
                           Join Filter: ("inner".cod_modele = 
"outer".cod_modele)
                           ->  Hash Join  (cost=161.57..185.76 rows=2 
width=396) (actual time=535.31..549.63 rows=5600 loops=1)
                                 Hash Cond: ("outer".cod_modele = 
"inner".cod_modele)
                                 ->  Seq Scan on lm05_t_composition e  
(cost=0.00..22.50 rows=333 width=4) (actual time=1.75..3.23 rows=573 loops=1)
                                       Filter: (nb_vantaux >= 2)
                                 ->  Hash  (cost=161.57..161.57 rows=1 
width=392) (actual time=531.89..531.89 rows=0 loops=1)
                                       ->  Hash Join  (cost=68.10..161.57 
rows=1 width=392) (actual time=418.18..530.57 rows=1120 loops=1)
                                             Hash Cond: ("outer".cod_modele = 
"inner".cod_modele)
                                             Join Filter: 
("outer".cod_tarif_panneau = "inner".cod_tarif_panneau)
                                             ->  Hash Join  (cost=32.35..125.82 
rows=1 width=198) (actual time=229.95..333.14 rows=48 loops=1)
                                                   Hash Cond: 
("outer".cod_modele = "inner".cod_modele)
                                                   ->  Seq Scan on 
lm05_t_tarif_panneau g  (cost=0.00..90.00 rows=692 width=190) (actual 
time=0.31..295.15 rows=4062 loops=1)
                                                         Filter: (((lrg_min < 
1000) AND (lrg_max >= 1000)) OR ((lrg_min < 500) AND (lrg_max >= 500)) OR 
(((lrg_min)::numeric < 333.333333333333) AND ((lrg_max)::numeric >= 
333.333333333333)) OR ((lrg_min < 250) AND (lrg_max >= 250)) OR ((lrg_min < 
200) AND (lrg_max >= 200)) OR (((lrg_min)::numeric <166.666666666667) AND 
((lrg_max)::numeric >= 166.666666666667)) OR (((lrg_min)::numeric < 
142.857142857143) AND ((lrg_max)::numeric >= 142.857142857143)) OR ((lrg_min < 
125) AND (lrg_max >= 125)) OR (((lrg_min)::numeric < 111.111111111111) AND 
((lrg_max)::numeric >= 111.111111111111)) OR ((lrg_min < 100) AND (lrg_max >= 
100)))
                                                   ->  Hash  (cost=32.35..32.35 
rows=1 width=8) (actual time=34.88..34.88 rows=0 loops=1)
                                                         ->  Nested Loop  
(cost=0.00..32.35 rows=1 width=8) (actual time=33.34..34.87 rows=1 loops=1)
                                                               ->  Seq Scan on 
cm_gestion_modele_ca h  (cost=0.00..27.50 rows=1 width=4) (actual 
time=0.14..25.64 rows=165 loops=1)
                                                                     Filter: 
((idmagasin = '011'::character varying) AND (idoav = 'PC_PLACARD'::character 
varying) AND (autorise = 1))
                                                               ->  Index Scan 
using lm05_t_modele_cod_modele_key on lm05_t_modele a  (cost=0.00..4.83 rows=1 
width=4) (actual time=0.05..0.05 rows=0 loops=165)
                                                                     Index 
Cond: ("outer".cod_modele = a.cod_modele)
                                                                     Filter: 
((cod_type_ouverture = 'OUV_COU'::character varying) AND (cod_type_panneau = 
'PAN_MEL'::character varying) AND (cod_fournisseur = 5132) AND (cod_gamme_prof 
= 'Design Xtra'::character varying))
                                             ->  Hash  (cost=35.75..35.75 
rows=1 width=194) (actual time=186.04..186.04 rows=0 loops=1)
                                                   ->  Hash Join  
(cost=30.00..35.75 rows=1 width=194) (actual time=185.26..185.77 rows=280 
loops=1)
                                                         Hash Cond: 
("outer".cod_modele = "inner".cod_modele)
                                                         ->  Seq Scan on 
mag_gestion_modele_mag i  (cost=0.00..4.91 rows=166 width=4) (actual 
time=0.05..1.15 rows=166 loops=1)
                                                               Filter: ((idmagas
in = '011'::character varying) AND (idoav = 'PC_PLACARD'::character varying) AND
 (selection = 1))
                                                         ->  Hash  
(cost=30.00..30.00 rows=1 width=190) (actual time=183.99..183.99 rows=0 loops=1)
                                                               ->  Seq Scan on 
lm05_t_couleur_panneau c  (cost=0.00..30.00 rows=1 width=190) (actual 
time=146.95..183.67 rows=280 loops=1)
                                                                     Filter: 
((ht_min < 2000) AND (ht_max >= 2000) AND (cod_aspect = 'tons bois et 
cuirs'::character varying) AND (cod_gamme_panneau = 'BOIS et CUIR XTRA 
3'::character varying))
                           ->  Hash  (cost=22.50..22.50 rows=5 width=97) 
(actual time=46.82..46.82 rows=0 loops=1)
                                 ->  Seq Scan on lm05_t_couleur_profile f  
(cost=0.00..22.50 rows=5 width=97) (actual time=32.07..46.77 rows=32 loops=1)
                                       Filter: (couleur_profile = 'acajou 
mat'::character varying)
 Total runtime: 840.40 msec
(42 rows)

CCM=# 

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to