Thread: Execution plan changed after upgrade from 7.3.9 to 8.2.3

Execution plan changed after upgrade from 7.3.9 to 8.2.3

From
Date:
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'internetne permettant pas d'assurer l'intégrité de ce message, le contenu de ce message ne représente en aucun cas un
engagementde la part de Leroy Merlin. 


Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3

From
Michael Fuhr
Date:
On Tue, Mar 13, 2007 at 09:19:47AM +0100, vincent.moreau@leroymerlin.fr 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?

--
Michael Fuhr

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, vincent.moreau@leroymerlin.fr 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'internetne permettant pas d'assurer l'intégrité de ce message, le contenu de ce message ne représente en aucun cas un
engagementde 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_modeleAS D, lm05_t_tarif_panneau AS G , lm05_t_composition AS E , lm05_t_couleur_profile AS F ,
cm_gestion_modele_caas 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_modeleAND 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
ANDG.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
ANDI.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=1120loops=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=1120loops=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.375rows=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.078rows=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=19097width=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=1loops=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)
(actualtime=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.152rows=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.205rows=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.465rows=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=165loops=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_modeleAS D, lm05_t_tarif_panneau AS G , lm05_t_composition AS E , lm05_t_couleur_profile AS F ,
cm_gestion_modele_caas 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_modeleAND 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
ANDG.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
ANDI.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=165loops=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.23rows=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=1120loops=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.14rows=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.88rows=0 loops=1)
 
                                                         ->  Nested Loop  (cost=0.00..32.35 rows=1 width=8) (actual
time=33.34..34.87rows=1 loops=1)
 
                                                               ->  Seq Scan on cm_gestion_modele_ca h
(cost=0.00..27.50rows=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_modelea  (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'::charactervarying) 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=0loops=1)
 
                                                   ->  Hash Join  (cost=30.00..35.75 rows=1 width=194) (actual
time=185.26..185.77rows=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=166width=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.99rows=0 loops=1)
 
                                                               ->  Seq Scan on lm05_t_couleur_panneau c
(cost=0.00..30.00rows=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.77rows=32 loops=1)
 
                                       Filter: (couleur_profile = 'acajou mat'::character varying)
 Total runtime: 840.40 msec
(42 rows)

CCM=# 


Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3

From
Richard Huxton
Date:
vincent.moreau@leroymerlin.fr wrote:
> 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.

->  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)

It does seem to be running that sequential scan 280 times, which is a
strange choice to say the least.

Obvious thing #1 is to look at I'd say is the stats on lrg_min,lrg_max -
try something like:
ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_min SET STATISTICS <n>
You can set <n> up to 1000 (and then the same for lrg_max of course).
Analyse the table again and see if that gives it a clue.

Second thing might be to try indexes on lrg_min and lrg_max and see if
the bitmap code in 8.2 helps things.

Very strange plan.
--
   Richard Huxton
   Archonet Ltd

Thanks for the update.

The following did not change anything in the execution plan

ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_min SET STATISTICS 1000
ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_max SET STATISTICS 1000
ANALYZE lm05_t_tarif_panneau

I was able to improve response time by creating indexes, but I would
like to avoid changing the database structure because it is not
maintained by ourseleves, but by the  third party vendor.



Richard Huxton wrote:
> vincent.moreau@leroymerlin.fr wrote:
>> 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.
>
> ->  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)
>
> It does seem to be running that sequential scan 280 times, which is a
> strange choice to say the least.
>
> Obvious thing #1 is to look at I'd say is the stats on lrg_min,lrg_max
> - try something like:
> ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_min SET STATISTICS <n>
> You can set <n> up to 1000 (and then the same for lrg_max of course).
> Analyse the table again and see if that gives it a clue.
>
> Second thing might be to try indexes on lrg_min and lrg_max and see if
> the bitmap code in 8.2 helps things.
>
> Very strange plan.


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'internetne permettant pas d'assurer l'intégrité de ce message, le contenu de ce message ne représente en aucun cas un
engagementde la part de Leroy Merlin. 


Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3

From
Alvaro Herrera
Date:
vincent.moreau@leroymerlin.fr wrote:
> 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.

There are only nested loops and hash joins, while the other plan seems
to be more elaborate -- I wonder if you have disabled bitmap scan, merge
joins, in 8.2?   Try a SHOW enable_mergejoin in psql.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3

From
"Dave Dutcher"
Date:
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> vincent.moreau@leroymerlin.fr
> Subject: Re: [PERFORM] Execution plan changed after upgrade
> from 7.3.9 to 8.2.3
>
> The following did not change anything in the execution plan
>
> ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_min SET
> STATISTICS 1000
> ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_max SET
> STATISTICS 1000
> ANALYZE lm05_t_tarif_panneau
>
> I was able to improve response time by creating indexes, but I would
> like to avoid changing the database structure because it is not
> maintained by ourseleves, but by the  third party vendor.


I would actually try increasing the statistics on table
lm05_t_couleur_panneau columns ht_min, ht_max, cod_aspect, and
cod_gamme_panneau.  Because I think the planner is thrown off because the
sequential scan on lm05_t_couleur_panneau returns 280 rows when it expects
1.  Maybe to start you could just SET default_statistics_target=1000,
analyze everything, and see if that makes any difference.

Dave


Here it is :

CCM=# SHOW enable_mergejoin;
enable_mergejoin
------------------
on
(1 row)

CCM=#




Alvaro Herrera wrote:
> vincent.moreau@leroymerlin.fr wrote:
>
>> 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.
>>
>
> There are only nested loops and hash joins, while the other plan seems
> to be more elaborate -- I wonder if you have disabled bitmap scan, merge
> joins, in 8.2?   Try a SHOW enable_mergejoin in psql.
>
>


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'internetne permettant pas d'assurer l'intégrité de ce message, le contenu de ce message ne représente en aucun cas un
engagementde la part de Leroy Merlin. 


Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3

From
Richard Huxton
Date:
vincent.moreau@leroymerlin.fr wrote:
> Thanks for the update.
>
> The following did not change anything in the execution plan
>
> ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_min SET STATISTICS 1000
> ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_max SET STATISTICS 1000
> ANALYZE lm05_t_tarif_panneau

Hmm - so it's not the distribution of those values.

> I was able to improve response time by creating indexes, but I would
> like to avoid changing the database structure because it is not
> maintained by ourseleves, but by the  third party vendor.

Well, the indexes can't do any harm, but it would be nice not to need them.

Could you post the explain analyse with the indexes? To see how the
costs compare.

--
   Richard Huxton
   Archonet Ltd

Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3

From
Alvaro Herrera
Date:
vincent.moreau@leroymerlin.fr wrote:
> Here it is :
>
> CCM=# SHOW enable_mergejoin;
> enable_mergejoin
> ------------------
> on
> (1 row)

Sorry, my question was more general.  Do you have _any_ of the planner
types disabled?  Try also enable_indexscan, etc; maybe

select * from pg_settings where name like 'enable_%';

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Increasing the default_statistics_target to 1000 did not help.
It just make the vacuum full analyze to take longer to complete.

Here is the output :

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=5275.40..5275.42 rows=1 width=32) (actual
time=21566.453..21568.917 rows=140 loops=1)
-> Sort (cost=5275.40..5275.41 rows=1 width=32) (actual
time=21566.450..21567.212 rows=1400 loops=1)
Sort Key: c.cod_couleur_panneau, c.cod_couleur_panneau,
'LM05'::character varying, 'OMM_TEINTE'::character varyin
g, 'IM'::character varying
-> Nested Loop (cost=105.58..5275.39 rows=1 width=32) (actual
time=94.901..21534.435 rows=1400 loops=1)
Join Filter: (a.cod_modele = d.cod_modele)
-> Nested Loop (cost=105.58..5267.27 rows=1 width=60) (actual
time=94.700..21213.793 rows=1400 loops=1)
Join Filter: (a.cod_modele = e.cod_modele)
-> Nested Loop (cost=105.58..5245.28 rows=1 width=56) (actual
time=93.912..20996.857 rows=280 loops
=1)
Join Filter: (h.cod_modele = a.cod_modele)
-> Nested Loop (cost=105.58..4731.94 rows=1 width=52) (actual
time=86.994..19181.638 rows=280
loops=1)
Join Filter: (i.cod_modele = a.cod_modele)
-> Nested Loop (cost=105.58..4721.10 rows=1 width=48) (actual
time=86.651..19091.147 ro
ws=280 loops=1)
Join Filter: ((a.cod_modele = c.cod_modele) AND
((g.cod_tarif_panneau)::text = (c.c
od_tarif_panneau)::text) AND ((f.cod_tarif_profile)::text =
(g.cod_tarif_profile)::text))
-> Hash Join (cost=99.26..1665.04 rows=1 width=84) (actual
time=25.598..31.845 ro
ws=280 loops=1)
Hash Cond: (c.cod_modele = f.cod_modele)
-> Seq Scan on lm05_t_couleur_panneau c (cost=0.00..1565.60 rows=4 width=62
) (actual time=23.817..29.048 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 (cost=98.86..98.86 rows=32 width=22) (actual time=1.653..1.653 rows
=32 loops=1)
-> Seq Scan on lm05_t_couleur_profile f (cost=0.00..98.86 rows=32 wid
th=22) (actual time=1.159..1.614 rows=32 loops=1)
Filter: ((couleur_profile)::text = 'acajou mat'::text)
-> Hash Join (cost=6.31..3054.10 rows=112 width=48) (actual
time=58.304..68.027 r
ows=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=18557 width=
44) (actual time=0.009..65.642 rows=4062 loops=280)
Filter: (((lrg_min < 1000) AND (lrg_max >= 1000)) OR ((lrg_min < 500) A
ND (lrg_max >= 500)) OR (((lrg_min)::numeric < 333.333333333333) AND
((lrg_max)::numeric >= 333.333333333333)) OR ((lrg_mi
n < 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.857142
857143)) 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.118..0.118 rows=1 l
oops=1)
-> Seq Scan on lm05_t_modele a (cost=0.00..6.30 rows=1 width=4) (actu
al time=0.039..0.110 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.059..0.224 rows=165 loops=280)
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=165
width=4) (actual time=0.032
..6.379 rows=165 loops=280)
Filter: (((idmagasin)::text = '011'::text) AND ((idoav)::text =
'PC_PLACARD'::text) AND (
autorise = 1))
-> Seq Scan on lm05_t_composition e (cost=0.00..14.82 rows=573 width=4)
(actual time=0.010..0.452 r
ows=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=16
5 loops=1400)
Filter: (largeur_maxi_rail >= 1000)
Total runtime: 21569.332 ms
(36 rows)

CCM=#



Dave Dutcher wrote:
>> From: pgsql-performance-owner@postgresql.org
>> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
>> vincent.moreau@leroymerlin.fr
>> Subject: Re: [PERFORM] Execution plan changed after upgrade
>> from 7.3.9 to 8.2.3
>>
>> The following did not change anything in the execution plan
>>
>> ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_min SET
>> STATISTICS 1000
>> ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_max SET
>> STATISTICS 1000
>> ANALYZE lm05_t_tarif_panneau
>>
>> I was able to improve response time by creating indexes, but I would
>> like to avoid changing the database structure because it is not
>> maintained by ourseleves, but by the  third party vendor.
>>
>
>
> I would actually try increasing the statistics on table
> lm05_t_couleur_panneau columns ht_min, ht_max, cod_aspect, and
> cod_gamme_panneau.  Because I think the planner is thrown off because the
> sequential scan on lm05_t_couleur_panneau returns 280 rows when it expects
> 1.  Maybe to start you could just SET default_statistics_target=1000,
> analyze everything, and see if that makes any difference.
>
> Dave
>
>
>


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'internetne permettant pas d'assurer l'intégrité de ce message, le contenu de ce message ne représente en aucun cas un
engagementde la part de Leroy Merlin. 


All planner types were enabled.

CCM=# select * from pg_settings where name like 'enable_%';
       name        | setting | unit |                  category                   |                       short_desc
                  | extra_desc | context | vartype | source  | min_val | max_val  

-------------------+---------+------+---------------------------------------------+--------------------------------------------------------+------------+---------+---------+---------+---------+---------
 enable_bitmapscan | on      |      | Query Tuning / Planner Method Configuration | Enables the planner's use of
bitmap-scanplans.        |            | user    | bool    | default |         |  
 enable_hashagg    | on      |      | Query Tuning / Planner Method Configuration | Enables the planner's use of hashed
aggregationplans. |            | user    | bool    | default |         |  
 enable_hashjoin   | on      |      | Query Tuning / Planner Method Configuration | Enables the planner's use of hash
joinplans.          |            | user    | bool    | default |         |  
 enable_indexscan  | on      |      | Query Tuning / Planner Method Configuration | Enables the planner's use of
index-scanplans.         |            | user    | bool    | default |         |  
 enable_mergejoin  | on      |      | Query Tuning / Planner Method Configuration | Enables the planner's use of merge
joinplans.         |            | user    | bool    | default |         |  
 enable_nestloop   | on      |      | Query Tuning / Planner Method Configuration | Enables the planner's use of
nested-loopjoin plans.   |            | user    | bool    | default |         |  
 enable_seqscan    | on      |      | Query Tuning / Planner Method Configuration | Enables the planner's use of
sequential-scanplans.    |            | user    | bool    | default |         |  
 enable_sort       | on      |      | Query Tuning / Planner Method Configuration | Enables the planner's use of
explicitsort steps.      |            | user    | bool    | default |         |  
 enable_tidscan    | on      |      | Query Tuning / Planner Method Configuration | Enables the planner's use of TID
scanplans.           |            | user    | bool    | default |         |  
(9 rows)



I was able to improve response time by seting enable_seqscan to off

Here is the new analyze result :

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=700005413.95..700005413.97 rows=1 width=32) (actual
time=1232.497..1234.961 rows=140 loops=1)
-> Sort (cost=700005413.95..700005413.96 rows=1 width=32) (actual
time=1232.494..1233.231 rows=1400 loops=1)
Sort Key: c.cod_couleur_panneau, c.cod_couleur_panneau,
'LM05'::character varying, 'OMM_TEINTE'::character varying,
'IM'::character va
rying
-> Hash Join (cost=700002228.09..700005413.94 rows=1 width=32) (actual
time=1192.211..1204.675 rows=1400 loops=1)
Hash Cond: ((g.cod_modele = a.cod_modele) AND
((g.cod_tarif_profile)::text = (f.cod_tarif_profile)::text) AND
((g.cod_tarif_pann
eau)::text = (c.cod_tarif_panneau)::text))
-> Seq Scan on lm05_t_tarif_panneau g (cost=100000000.00..100002977.08
rows=18557 width=44) (actual time=0.038..69.017 rows=40
62 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)::num
eric >= 111.111111111111)) OR ((lrg_min < 100) AND (lrg_max >= 100)))
-> Hash (cost=600002228.07..600002228.07 rows=1 width=104) (actual
time=1129.717..1129.717 rows=700 loops=1)
-> Nested Loop (cost=600001665.30..600002228.07 rows=1 width=104)
(actual time=43.012..1127.646 rows=700 loops=1)
Join Filter: (a.cod_modele = e.cod_modele)
-> Nested Loop (cost=500001665.30..500002206.08 rows=1 width=100)
(actual time=42.246..1020.245 rows=140 loops=1)
Join Filter: (a.cod_modele = d.cod_modele)
-> Nested Loop (cost=400001665.30..400002197.96 rows=1 width=96) (actual
time=42.032..986.021 rows=140 loops
=1)
Join Filter: (h.cod_modele = a.cod_modele)
-> Nested Loop (cost=300001665.30..300001684.62 rows=1 width=92) (actual
time=35.244..83.822 rows=140
loops=1)
Join Filter: (i.cod_modele = a.cod_modele)
-> Nested Loop (cost=200001665.30..200001673.78 rows=1 width=88) (actual
time=34.916..39.601 row
s=140 loops=1)
-> Merge Join (cost=200001665.30..200001665.49 rows=1 width=84) (actual
time=34.806..36.35
2 rows=280 loops=1)
Merge Cond: (c.cod_modele = f.cod_modele)
-> Sort (cost=100001565.64..100001565.65 rows=4 width=62) (actual
time=32.859..33.07
7 rows=280 loops=1)
Sort Key: c.cod_modele
-> Seq Scan on lm05_t_couleur_panneau c (cost=100000000.00..100001565.60
rows=
4 width=62) (actual time=27.553..32.501 rows=280 loops=1)
Filter: ((ht_min < 2000) AND (ht_max >= 2000) AND ((cod_aspect)::text = 't
ons bois et cuirs'::text) AND ((cod_gamme_panneau)::text = 'BOIS et CUIR
XTRA 3'::text))
-> Sort (cost=100000099.66..100000099.74 rows=32 width=22) (actual
time=1.909..2.188
rows=308 loops=1)
Sort Key: f.cod_modele
-> Seq Scan on lm05_t_couleur_profile f (cost=100000000.00..100000098.86
rows=
32 width=22) (actual time=1.268..1.828 rows=32 loops=1)
Filter: ((couleur_profile)::text = 'acajou mat'::text)
-> Index Scan using lm05_t_modele_cod_modele_key on lm05_t_modele a
(cost=0.00..8.28 rows=
1 width=4) (actual time=0.007..0.009 rows=0 loops=280)
Index Cond: (a.cod_modele = c.cod_modele)
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=100000000.00..100000008.78
rows=165 width=4) (actu
al time=0.056..0.220 rows=165 loops=140)
Filter: (((idmagasin)::text = '011'::text) AND ((idoav)::text =
'PC_PLACARD'::text) AND (sel
ection = 1))
-> Seq Scan on cm_gestion_modele_ca h (cost=100000000.00..100000511.28
rows=165 width=4) (actual time=
0.031..6.343 rows=165 loops=140)
Filter: (((idmagasin)::text = '011'::text) AND ((idoav)::text =
'PC_PLACARD'::text) AND (autorise
= 1))
-> Seq Scan on lm05_t_infos_modele d (cost=100000000.00..100000006.06
rows=165 width=4) (actual time=0.009..
0.149 rows=165 loops=140)
Filter: (largeur_maxi_rail >= 1000)
-> Seq Scan on lm05_t_composition e (cost=100000000.00..100000014.83
rows=573 width=4) (actual time=0.007..0.445 r
ows=573 loops=140)
Filter: (nb_vantaux >= 2)
Total runtime: 1235.660 ms
(39 rows)





Alvaro Herrera wrote:
> vincent.moreau@leroymerlin.fr wrote:
>
>> Here it is :
>>
>> CCM=# SHOW enable_mergejoin;
>> enable_mergejoin
>> ------------------
>> on
>> (1 row)
>>
>
> Sorry, my question was more general.  Do you have _any_ of the planner
> types disabled?  Try also enable_indexscan, etc; maybe
>
> select * from pg_settings where name like 'enable_%';
>
>


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'internetne permettant pas d'assurer l'intégrité de ce message, le contenu de ce message ne représente en aucun cas un
engagementde la part de Leroy Merlin. 


Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3

From
"Dave Dutcher"
Date:
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> vincent.moreau@leroymerlin.fr
> Subject: Re: [PERFORM] Execution plan changed after upgrade
> from 7.3.9 to 8.2.3
>
>
> Increasing the default_statistics_target to 1000 did not help.
> It just make the vacuum full analyze to take longer to complete.

Just FYI when you change statistics you only need to run ANALYZE, not VACUUM
ANALYZE, and definetly not VACUUM FULL ANALYZE.

I don't know what else to suggest for this query since you can't change the
SQL.  I would talk to the vendor and ask them to add indexes if you know
that helps.

Dave


Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3

From
Tom Lane
Date:
<vincent.moreau@leroymerlin.fr> writes:
> I was able to improve response time by seting enable_seqscan to off

enable_nestloop = off would probably be a saner choice, at least for
this particular query.

            regards, tom lane

Thanks for the advice Tom !

Setting enable_nestloop = off did improve the query a much better way
than setting enable_seqscan to off.

It does not screw the costs either (I had very odd costs with
enable_seqscan to off like this : Nested Loop
(cost=400001665.30..400002197.96 rows=1 width=96)

Is there a "performance risk" to have enable_nestloop = off for other
queries ?

If I had the choice, should I go for index creation for the specific
tables or should I tweak the optimizer with enable_nestloop = off ?


Thanks again to all of you for your help.

Best Regards,
Vincent

Tom Lane wrote:
> <vincent.moreau@leroymerlin.fr> writes:
>
>> I was able to improve response time by seting enable_seqscan to off
>>
>
> enable_nestloop = off would probably be a saner choice, at least for
> this particular query.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>


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'internetne permettant pas d'assurer l'intégrité de ce message, le contenu de ce message ne représente en aucun cas un
engagementde la part de Leroy Merlin.