From: vincent.moreau@leroymerlin.fr
To: pgsql-performance@postgresql.org
Subject: Execution plan changed after upgrade from 7.3.9 to 8.2.3
Date: Tue, 13 Mar 2007 09:19:47 +0100
Message-ID: <45F65EA3.1030706@leroymerlin.fr> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: vincent.moreau@leroymerlin.fr
Subject: Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3
In-Reply-To: <45F65EA3.1030706@leroymerlin.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox