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.
