Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3

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-scan plans. | | user | bool | default | |
enable_hashagg | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of hashed aggregation plans. | | user | bool | default | |
enable_hashjoin | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of hash join plans. | | user | bool | default | |
enable_indexscan | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of index-scan plans. | | user | bool | default | |
enable_mergejoin | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of merge join plans. | | user | bool | default | |
enable_nestloop | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of nested-loop join plans. | | user | bool | default | |
enable_seqscan | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of sequential-scan plans. | | user | bool | default | |
enable_sort | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of explicit sort steps. | | user | bool | default | |
enable_tidscan | on | | Query Tuning / Planner Method Configuration | Enables the planner's use of TID scan plans. | | 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(dot)moreau(at)leroymerlin(dot)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'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.