Message-ID: <45F6AFA9.4020804@leroymerlin.fr>
Date: Tue, 13 Mar 2007 15:05:29 +0100
From: <vincent.moreau@leroymerlin.fr>
Organization: leroy merlin
User-Agent: Thunderbird 1.5.0.10 (X11/20070306)
MIME-Version: 1.0
To: vincent.moreau@leroymerlin.fr, Michael Fuhr <mike@fuhr.org>,
	pgsql-performance@postgresql.org
Subject: Re: Execution plan changed after upgrade from 7.3.9 to
 8.2.3
References: <45F65EA3.1030706@leroymerlin.fr>
	<20070313101117.GA98241@winnie.fuhr.org>
	<45F686FA.2090909@leroymerlin.fr>
	<20070313130945.GB4490@alvh.no-ip.org>
	<45F6A6F8.9090405@leroymerlin.fr>
	<20070313134306.GC6715@alvh.no-ip.org>
In-Reply-To: <20070313134306.GC6715@alvh.no-ip.org>
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=iso-8859-1

All planner types were enabled.

CCM=3D# select * from pg_settings where name like 'enable_%';
       name        | setting | unit |                  category            =
       |                       short_desc                       | extra_des=
c | context | vartype | source  | min_val | max_val=20
-------------------+---------+------+--------------------------------------=
-------+--------------------------------------------------------+----------=
--+---------+---------+---------+---------+---------
 enable_bitmapscan | on      |      | Query Tuning / Planner Method Configu=
ration | Enables the planner's use of bitmap-scan plans.        |          =
  | user    | bool    | default |         |=20
 enable_hashagg    | on      |      | Query Tuning / Planner Method Configu=
ration | Enables the planner's use of hashed aggregation plans. |          =
  | user    | bool    | default |         |=20
 enable_hashjoin   | on      |      | Query Tuning / Planner Method Configu=
ration | Enables the planner's use of hash join plans.          |          =
  | user    | bool    | default |         |=20
 enable_indexscan  | on      |      | Query Tuning / Planner Method Configu=
ration | Enables the planner's use of index-scan plans.         |          =
  | user    | bool    | default |         |=20
 enable_mergejoin  | on      |      | Query Tuning / Planner Method Configu=
ration | Enables the planner's use of merge join plans.         |          =
  | user    | bool    | default |         |=20
 enable_nestloop   | on      |      | Query Tuning / Planner Method Configu=
ration | Enables the planner's use of nested-loop join plans.   |          =
  | user    | bool    | default |         |=20
 enable_seqscan    | on      |      | Query Tuning / Planner Method Configu=
ration | Enables the planner's use of sequential-scan plans.    |          =
  | user    | bool    | default |         |=20
 enable_sort       | on      |      | Query Tuning / Planner Method Configu=
ration | Enables the planner's use of explicit sort steps.      |          =
  | user    | bool    | default |         |=20
 enable_tidscan    | on      |      | Query Tuning / Planner Method Configu=
ration | Enables the planner's use of TID scan plans.           |          =
  | user    | bool    | default |         |=20
(9 rows)


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

Here is the new analyze result :

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=3D700005413.95..700005413.97 rows=3D1 width=3D32) (actual=20
time=3D1232.497..1234.961 rows=3D140 loops=3D1)
-> Sort (cost=3D700005413.95..700005413.96 rows=3D1 width=3D32) (actual=20
time=3D1232.494..1233.231 rows=3D1400 loops=3D1)
Sort Key: c.cod_couleur_panneau, c.cod_couleur_panneau,=20
'LM05'::character varying, 'OMM_TEINTE'::character varying,=20
'IM'::character va
rying
-> Hash Join (cost=3D700002228.09..700005413.94 rows=3D1 width=3D32) (actua=
l=20
time=3D1192.211..1204.675 rows=3D1400 loops=3D1)
Hash Cond: ((g.cod_modele =3D a.cod_modele) AND=20
((g.cod_tarif_profile)::text =3D (f.cod_tarif_profile)::text) AND=20
((g.cod_tarif_pann
eau)::text =3D (c.cod_tarif_panneau)::text))
-> Seq Scan on lm05_t_tarif_panneau g (cost=3D100000000.00..100002977.08=20
rows=3D18557 width=3D44) (actual time=3D0.038..69.017 rows=3D40
62 loops=3D1)
Filter: (((lrg_min < 1000) AND (lrg_max >=3D 1000)) OR ((lrg_min < 500)=20
AND (lrg_max >=3D 500)) OR (((lrg_min)::numeric < 333.
333333333333) AND ((lrg_max)::numeric >=3D 333.333333333333)) OR ((lrg_min=
=20
< 250) AND (lrg_max >=3D 250)) OR ((lrg_min < 200) AND (lrg_max >=3D 200))
OR (((lrg_min)::numeric < 166.666666666667) AND ((lrg_max)::numeric >=3D=20
166.666666666667)) OR (((lrg_min)::numeric < 142.857142857143) AND ((lr
g_max)::numeric >=3D 142.857142857143)) OR ((lrg_min < 125) AND (lrg_max=20
 >=3D 125)) OR (((lrg_min)::numeric < 111.111111111111) AND ((lrg_max)::num
eric >=3D 111.111111111111)) OR ((lrg_min < 100) AND (lrg_max >=3D 100)))
-> Hash (cost=3D600002228.07..600002228.07 rows=3D1 width=3D104) (actual=20
time=3D1129.717..1129.717 rows=3D700 loops=3D1)
-> Nested Loop (cost=3D600001665.30..600002228.07 rows=3D1 width=3D104)=20
(actual time=3D43.012..1127.646 rows=3D700 loops=3D1)
Join Filter: (a.cod_modele =3D e.cod_modele)
-> Nested Loop (cost=3D500001665.30..500002206.08 rows=3D1 width=3D100)=20
(actual time=3D42.246..1020.245 rows=3D140 loops=3D1)
Join Filter: (a.cod_modele =3D d.cod_modele)
-> Nested Loop (cost=3D400001665.30..400002197.96 rows=3D1 width=3D96) (act=
ual=20
time=3D42.032..986.021 rows=3D140 loops
=3D1)
Join Filter: (h.cod_modele =3D a.cod_modele)
-> Nested Loop (cost=3D300001665.30..300001684.62 rows=3D1 width=3D92) (act=
ual=20
time=3D35.244..83.822 rows=3D140
loops=3D1)
Join Filter: (i.cod_modele =3D a.cod_modele)
-> Nested Loop (cost=3D200001665.30..200001673.78 rows=3D1 width=3D88) (act=
ual=20
time=3D34.916..39.601 row
s=3D140 loops=3D1)
-> Merge Join (cost=3D200001665.30..200001665.49 rows=3D1 width=3D84) (actu=
al=20
time=3D34.806..36.35
2 rows=3D280 loops=3D1)
Merge Cond: (c.cod_modele =3D f.cod_modele)
-> Sort (cost=3D100001565.64..100001565.65 rows=3D4 width=3D62) (actual=20
time=3D32.859..33.07
7 rows=3D280 loops=3D1)
Sort Key: c.cod_modele
-> Seq Scan on lm05_t_couleur_panneau c (cost=3D100000000.00..100001565.60=
=20
rows=3D
4 width=3D62) (actual time=3D27.553..32.501 rows=3D280 loops=3D1)
Filter: ((ht_min < 2000) AND (ht_max >=3D 2000) AND ((cod_aspect)::text =3D=
 't
ons bois et cuirs'::text) AND ((cod_gamme_panneau)::text =3D 'BOIS et CUIR=
=20
XTRA 3'::text))
-> Sort (cost=3D100000099.66..100000099.74 rows=3D32 width=3D22) (actual=20
time=3D1.909..2.188
rows=3D308 loops=3D1)
Sort Key: f.cod_modele
-> Seq Scan on lm05_t_couleur_profile f (cost=3D100000000.00..100000098.86=
=20
rows=3D
32 width=3D22) (actual time=3D1.268..1.828 rows=3D32 loops=3D1)
Filter: ((couleur_profile)::text =3D 'acajou mat'::text)
-> Index Scan using lm05_t_modele_cod_modele_key on lm05_t_modele a=20
(cost=3D0.00..8.28 rows=3D
1 width=3D4) (actual time=3D0.007..0.009 rows=3D0 loops=3D280)
Index Cond: (a.cod_modele =3D c.cod_modele)
Filter: (((cod_type_ouverture)::text =3D 'OUV_COU'::text) AND=20
((cod_type_panneau)::text
=3D 'PAN_MEL'::text) AND (cod_fournisseur =3D 5132) AND=20
((cod_gamme_prof)::text =3D 'Design Xtra'::text))
-> Seq Scan on mag_gestion_modele_mag i (cost=3D100000000.00..100000008.78=
=20
rows=3D165 width=3D4) (actu
al time=3D0.056..0.220 rows=3D165 loops=3D140)
Filter: (((idmagasin)::text =3D '011'::text) AND ((idoav)::text =3D=20
'PC_PLACARD'::text) AND (sel
ection =3D 1))
-> Seq Scan on cm_gestion_modele_ca h (cost=3D100000000.00..100000511.28=20
rows=3D165 width=3D4) (actual time=3D
0.031..6.343 rows=3D165 loops=3D140)
Filter: (((idmagasin)::text =3D '011'::text) AND ((idoav)::text =3D=20
'PC_PLACARD'::text) AND (autorise
=3D 1))
-> Seq Scan on lm05_t_infos_modele d (cost=3D100000000.00..100000006.06=20
rows=3D165 width=3D4) (actual time=3D0.009..
0.149 rows=3D165 loops=3D140)
Filter: (largeur_maxi_rail >=3D 1000)
-> Seq Scan on lm05_t_composition e (cost=3D100000000.00..100000014.83=20
rows=3D573 width=3D4) (actual time=3D0.007..0.445 r
ows=3D573 loops=3D140)
Filter: (nb_vantaux >=3D 2)
Total runtime: 1235.660 ms
(39 rows)


Alvaro Herrera wrote:
> vincent.moreau@leroymerlin.fr wrote:
>=20=20=20
>> Here it is :
>>
>> CCM=3D# SHOW enable_mergejoin;
>> enable_mergejoin
>> ------------------
>> on
>> (1 row)
>>=20=20=20=20=20
>
> 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_%';
>
>=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.