Re: 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.

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(at)postgresql(dot)org
>> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of
>> vincent(dot)moreau(at)leroymerlin(dot)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'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.