Message-ID: <45F65EA3.1030706@leroymerlin.fr>
Date: Tue, 13 Mar 2007 09:19:47 +0100
From: <vincent.moreau@leroymerlin.fr>
Organization: leroy merlin
User-Agent: Thunderbird 1.5.0.10 (X11/20070306)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Execution plan changed after upgrade from 7.3.9 to 8.2.3
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Hello,


I have upgraded from 7.3.9 to 8.2.3 and now the application that is 
using Postgres is really slow.
Using pgfouine, I was able to identify a SQL select statement that was 
running in 500 ms before and now that is running in more than 20 seconds !

The reason is that the execution plan is different from the 2 versions.
The difference is the order the tables are joined :

For 8.2.3 :
Seq Scan on lm05_t_tarif_panneau g (cost=0.00..2977.08 rows=18947 
width=43) (actual time=0.006..65.388 rows=4062 loops=280)

For 7.3.9 :
Seq Scan on lm05_t_tarif_panneau g  (cost=0.00..90.00 rows=692 
width=190) (actual time=0.03..206.23 rows=4062 loops=1)

Is there an option in the 8.2.3 to change in order to have the same 
execution plan than before ?
I have compared the 2 postgresql.conf files and there are no differences 
as far as I know.

Thanks for your help.

Best Regards,
Vincent Moreau


For 7.3.9 :

Unique  (cost=232.48..232.51 rows=1 width=497) (actual 
time=524.49..543.00 rows=140 loops=1)
....

                                                  ->  Seq Scan on 
lm05_t_tarif_panneau g  (cost=0.00..90.00 rows=692 width=190) (actual 
time=0.03..206.23 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 ((lr
g_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=19.07..19.07 rows=0 
loops=1)
                                                        ->  Nested Loop  
(cost=0.00..32.35 rows=1 width=8) (actual time=17.99..19.07 rows=1 loops=1)
                                                              ->  Seq 
Scan on cm_gestion_modele_ca h  (cost=0.00..27.50 rows=1 width=4) 
(actual time=0.09..17.35 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.01..0.01 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))


For 8.2.3 :

Unique (cost=5278.93..5278.95 rows=1 width=32) (actual 
time=27769.435..27771.863 rows=140 loops=1)

...

-> Hash Join (cost=6.31..3055.59 rows=115 width=47) (actual 
time=58.096..67.787 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=18947 
width=43) (actual time=0.006..65.388 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.135..0.135 
rows=1 loops=1)

-> Seq Scan on lm05_t_modele a (cost=0.00..6.30 rows=1 width=4) (actual 
time=0.053..0.124 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.053..0.214 rows=165 loops=1120)

Filter: (((idmagasin)::text = '011'::text) AND ((idoav)::text = 
'PC_PLACARD'::text) AND (selection = 1))


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.