Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3 - Mailing list pgsql-performance

From
Subject Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3
Date
Msg-id 45F6AFA9.4020804@leroymerlin.fr
Whole thread Raw
In response to Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3  (Alvaro Herrera <alvherre@commandprompt.com>)
Responses Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
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. 


pgsql-performance by date:

Previous
From:
Date:
Subject: Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3
Next
From: "Dave Dutcher"
Date:
Subject: Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3