Message-ID: <45F6AF81.80201@leroymerlin.fr>
Date: Tue, 13 Mar 2007 15:04:49 +0100
From: <vincent.moreau@leroymerlin.fr>
Organization: leroy merlin
User-Agent: Thunderbird 1.5.0.10 (X11/20070306)
MIME-Version: 1.0
To: Dave Dutcher <dave@tridecap.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Execution plan changed after upgrade from 7.3.9 to
 8.2.3
References: <010401c76572$f33d33a0$2e00a8c0@tridecap.com>
In-Reply-To: <010401c76572$f33d33a0$2e00a8c0@tridecap.com>
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=iso-8859-1

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=3D# VACUUM FULL ANALYZE ;
VACUUM
CCM=3D# explain ANALYZE SELECT distinct C.cod_couleur_panneau,=20
C.cod_couleur_panneau, cast ('LM05' as varchar), cast ('OMM_TEINTE' as=20
varchar), cast ('IM' as varchar) FROM lm05_t_modele AS A,=20
lm05_t_couleur_panneau AS C, lm05_t_infos_modele AS D,=20
lm05_t_tarif_panneau AS G , lm05_t_composition AS E ,=20
lm05_t_couleur_profile AS F , cm_gestion_modele_ca as H,=20
mag_gestion_modele_mag as I WHERE A.cod_type_ouverture =3D 'OUV_COU' AND=20
A.cod_type_panneau =3D 'PAN_MEL' AND A.cod_modele =3D C.cod_modele AND=20
A.cod_modele =3D D.cod_modele AND A.cod_modele =3D G.cod_modele AND=20
G.cod_tarif_panneau =3D C.cod_tarif_panneau AND A.cod_modele =3D=20
E.cod_modele AND nb_vantaux >=3D 2 AND A.cod_modele =3D F.cod_modele AND=20
F.couleur_profile =3D 'acajou mat' AND F.cod_tarif_profile =3D=20
G.cod_tarif_profile AND A.cod_fournisseur =3D '5132' AND A.cod_gamme_prof=
=20
=3D 'Design Xtra' AND C.ht_min < 2000 AND C.ht_max >=3D 2000 AND=20
D.largeur_maxi_rail >=3D 1000 AND C.cod_aspect =3D 'tons bois et cuirs' AND=
=20
C.cod_gamme_panneau =3D 'BOIS et CUIR XTRA 3' AND ((G.lrg_min < 1000 AND=20
G.lrg_max >=3D 1000) OR (G.lrg_min < 500 AND G.lrg_max >=3D 500) OR=20
(G.lrg_min < 333.333333333333 AND G.lrg_max >=3D 333.333333333333) OR=20
(G.lrg_min < 250 AND G.lrg_max >=3D 250) OR (G.lrg_min < 200 AND G.lrg_max=
=20
 >=3D 200) OR (G.lrg_min < 166.666666666667 AND G.lrg_max >=3D=20
166.666666666667) OR (G.lrg_min < 142.857142857143 AND G.lrg_max >=3D=20
142.857142857143) OR (G.lrg_min < 125 AND G.lrg_max >=3D 125) OR=20
(G.lrg_min < 111.111111111111 AND G.lrg_max >=3D 111.111111111111) OR=20
(G.lrg_min < 100 AND G.lrg_max >=3D 100)) AND H.idmagasin =3D '011' AND=20
H.idoav=3D'PC_PLACARD' AND H.cod_modele =3D A.cod_modele AND H.autorise =3D=
 1=20
AND I.idmagasin =3D '011' AND I.idoav=3D'PC_PLACARD' AND I.cod_modele =3D=
=20
A.cod_modele AND I.selection =3D 1;


QUERY PLAN


---------------------------------------------------------------------------=
-----------------------------------------------
---------------------------------------------------------------------------=
-----------------------------------------------
---------------------------------------------------------------------------=
-----------------------------------------------
---------------------------------------------------------------------------=
-----------------------------------------------
---------------------------------------------------------------------------=
-----------------------------------------------
--------------------------------------------------------------
Unique (cost=3D5275.40..5275.42 rows=3D1 width=3D32) (actual=20
time=3D21566.453..21568.917 rows=3D140 loops=3D1)
-> Sort (cost=3D5275.40..5275.41 rows=3D1 width=3D32) (actual=20
time=3D21566.450..21567.212 rows=3D1400 loops=3D1)
Sort Key: c.cod_couleur_panneau, c.cod_couleur_panneau,=20
'LM05'::character varying, 'OMM_TEINTE'::character varyin
g, 'IM'::character varying
-> Nested Loop (cost=3D105.58..5275.39 rows=3D1 width=3D32) (actual=20
time=3D94.901..21534.435 rows=3D1400 loops=3D1)
Join Filter: (a.cod_modele =3D d.cod_modele)
-> Nested Loop (cost=3D105.58..5267.27 rows=3D1 width=3D60) (actual=20
time=3D94.700..21213.793 rows=3D1400 loops=3D1)
Join Filter: (a.cod_modele =3D e.cod_modele)
-> Nested Loop (cost=3D105.58..5245.28 rows=3D1 width=3D56) (actual=20
time=3D93.912..20996.857 rows=3D280 loops
=3D1)
Join Filter: (h.cod_modele =3D a.cod_modele)
-> Nested Loop (cost=3D105.58..4731.94 rows=3D1 width=3D52) (actual=20
time=3D86.994..19181.638 rows=3D280
loops=3D1)
Join Filter: (i.cod_modele =3D a.cod_modele)
-> Nested Loop (cost=3D105.58..4721.10 rows=3D1 width=3D48) (actual=20
time=3D86.651..19091.147 ro
ws=3D280 loops=3D1)
Join Filter: ((a.cod_modele =3D c.cod_modele) AND=20
((g.cod_tarif_panneau)::text =3D (c.c
od_tarif_panneau)::text) AND ((f.cod_tarif_profile)::text =3D=20
(g.cod_tarif_profile)::text))
-> Hash Join (cost=3D99.26..1665.04 rows=3D1 width=3D84) (actual=20
time=3D25.598..31.845 ro
ws=3D280 loops=3D1)
Hash Cond: (c.cod_modele =3D f.cod_modele)
-> Seq Scan on lm05_t_couleur_panneau c (cost=3D0.00..1565.60 rows=3D4 widt=
h=3D62
) (actual time=3D23.817..29.048 rows=3D280 loops=3D1)
Filter: ((ht_min < 2000) AND (ht_max >=3D 2000) AND ((cod_aspect)::text =3D
'tons bois et cuirs'::text) AND ((cod_gamme_panneau)::text =3D 'BOIS et=20
CUIR XTRA 3'::text))
-> Hash (cost=3D98.86..98.86 rows=3D32 width=3D22) (actual time=3D1.653..1.=
653 rows
=3D32 loops=3D1)
-> Seq Scan on lm05_t_couleur_profile f (cost=3D0.00..98.86 rows=3D32 wid
th=3D22) (actual time=3D1.159..1.614 rows=3D32 loops=3D1)
Filter: ((couleur_profile)::text =3D 'acajou mat'::text)
-> Hash Join (cost=3D6.31..3054.10 rows=3D112 width=3D48) (actual=20
time=3D58.304..68.027 r
ows=3D48 loops=3D280)
Hash Cond: (g.cod_modele =3D a.cod_modele)
-> Seq Scan on lm05_t_tarif_panneau g (cost=3D0.00..2977.08 rows=3D18557 wi=
dth=3D
44) (actual time=3D0.009..65.642 rows=3D4062 loops=3D280)
Filter: (((lrg_min < 1000) AND (lrg_max >=3D 1000)) OR ((lrg_min < 500) A
ND (lrg_max >=3D 500)) OR (((lrg_min)::numeric < 333.333333333333) AND=20
((lrg_max)::numeric >=3D 333.333333333333)) OR ((lrg_mi
n < 250) AND (lrg_max >=3D 250)) OR ((lrg_min < 200) AND (lrg_max >=3D 200)=
)=20
OR (((lrg_min)::numeric < 166.666666666667) AND (
(lrg_max)::numeric >=3D 166.666666666667)) OR (((lrg_min)::numeric <=20
142.857142857143) AND ((lrg_max)::numeric >=3D 142.857142
857143)) OR ((lrg_min < 125) AND (lrg_max >=3D 125)) OR=20
(((lrg_min)::numeric < 111.111111111111) AND ((lrg_max)::numeric >=3D
111.111111111111)) OR ((lrg_min < 100) AND (lrg_max >=3D 100)))
-> Hash (cost=3D6.30..6.30 rows=3D1 width=3D4) (actual time=3D0.118..0.118 =
rows=3D1 l
oops=3D1)
-> Seq Scan on lm05_t_modele a (cost=3D0.00..6.30 rows=3D1 width=3D4) (actu
al time=3D0.039..0.110 rows=3D1 loops=3D1)
Filter: (((cod_type_ouverture)::text =3D 'OUV_COU'::text) AND ((cod
_type_panneau)::text =3D 'PAN_MEL'::text) AND (cod_fournisseur =3D 5132) AN=
D=20
((cod_gamme_prof)::text =3D 'Design Xtra'::text))
-> Seq Scan on mag_gestion_modele_mag i (cost=3D0.00..8.78 rows=3D165=20
width=3D4) (actual time
=3D0.059..0.224 rows=3D165 loops=3D280)
Filter: (((idmagasin)::text =3D '011'::text) AND ((idoav)::text =3D=20
'PC_PLACARD'::text)
AND (selection =3D 1))
-> Seq Scan on cm_gestion_modele_ca h (cost=3D0.00..511.27 rows=3D165=20
width=3D4) (actual time=3D0.032
..6.379 rows=3D165 loops=3D280)
Filter: (((idmagasin)::text =3D '011'::text) AND ((idoav)::text =3D=20
'PC_PLACARD'::text) AND (
autorise =3D 1))
-> Seq Scan on lm05_t_composition e (cost=3D0.00..14.82 rows=3D573 width=3D=
4)=20
(actual time=3D0.010..0.452 r
ows=3D573 loops=3D280)
Filter: (nb_vantaux >=3D 2)
-> Seq Scan on lm05_t_infos_modele d (cost=3D0.00..6.06 rows=3D165 width=3D=
4)=20
(actual time=3D0.004..0.136 rows=3D16
5 loops=3D1400)
Filter: (largeur_maxi_rail >=3D 1000)
Total runtime: 21569.332 ms
(36 rows)

CCM=3D#


Dave Dutcher wrote:
>> From: pgsql-performance-owner@postgresql.org=20
>> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20
>> vincent.moreau@leroymerlin.fr
>> Subject: Re: [PERFORM] Execution plan changed after upgrade=20
>> 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=20
>> STATISTICS 1000
>> ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_max SET=20
>> STATISTICS 1000
>> ANALYZE lm05_t_tarif_panneau
>>
>> I was able to improve response time by creating indexes, but I would=20
>> like to avoid changing the database structure because it is not=20
>> maintained by ourseleves, but by the  third party vendor.
>>=20=20=20=20=20
>
>
> 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=3D1000,
> analyze everything, and see if that makes any difference.
>
> Dave
>
>
>=20=20=20


Ce message et toutes les pi=E8ces jointes sont =E9tablis =E0 l'attention ex=
clusive de leurs destinataires et sont confidentiels. Si vous recevez ce me=
ssage par erreur, merci de le d=E9truire et d'en avertir imm=E9diatement l'=
exp=E9diteur. L'internet ne permettant pas d'assurer l'int=E9grit=E9 de ce =
message, le contenu de ce message ne repr=E9sente en aucun cas un engagemen=
t de la part de Leroy Merlin.