X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 537089FB1F2 for ; Thu, 14 Sep 2006 11:27:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 75004-09 for ; Thu, 14 Sep 2006 14:27:17 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp-msa-out19.orange.fr (smtp19.orange.fr [80.12.242.17]) by postgresql.org (Postfix) with ESMTP id 318D69F9F2F for ; Thu, 14 Sep 2006 11:27:17 -0300 (ADT) Received: from [192.168.1.10] (LSt-Amand-152-32-6-178.w82-127.abo.wanadoo.fr [82.127.21.178]) by mwinf1911.orange.fr (SMTP Server) with ESMTP id 29A351C000E9; Thu, 14 Sep 2006 16:27:15 +0200 (CEST) X-ME-UUID: 20060914142715170.29A351C000E9@mwinf1911.orange.fr Subject: Re: High CPU Load From: =?ISO-8859-1?Q?J=E9r=F4me?= BENOIS To: Scott Marlowe Cc: Tom Lane , pgsql-performance@postgresql.org In-Reply-To: <1158243710.24726.12.camel@state.g2switchworks.com> References: <1158239339.5226.17.camel@localhost.localdomain> <28868.1158243217@sss.pgh.pa.us> <1158243436.5226.35.camel@localhost.localdomain> <1158243710.24726.12.camel@state.g2switchworks.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-i1ZUz+7ZHEqSxbosslF0" Date: Thu, 14 Sep 2006 16:27:12 +0200 Message-Id: <1158244032.5226.49.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.6.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/157 X-Sequence-Number: 20843 --=-i1ZUz+7ZHEqSxbosslF0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hello, Le jeudi 14 septembre 2006 =C3=A0 09:21 -0500, Scott Marlowe a =C3=A9crit : > On Thu, 2006-09-14 at 09:17, J=C3=A9r=C3=B4me BENOIS wrote: > > Hi Tom, > >=20 > > Le jeudi 14 septembre 2006 =C3=A0 10:13 -0400, Tom Lane a =C3=A9crit : > > > =3D?ISO-8859-1?Q?J=3DE9r=3DF4me?=3D BENOIS writes: > > > > I migrated Postgres server from 7.4.6 to 8.1.4, But my server is > > > > completely full, by moment load average > 40 > > >=20 > > > Did you remember to ANALYZE the whole database after reloading it? > > > pg_dump/reload won't by itself regenerate statistics. > > >=20 > > > regards, tom lane > > I tested, dump + restore + vaccumdb --analyze on all databases but no c= hange ... >=20 >=20 > OK, set your db to log queries that take more than a few seconds to > run. Execute those queries by hand with an explain analyze in front and > post the output here. >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend i tested all queries, but she used indexes ... an example : explain analyze select distinct INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_VALUE,CATEGOR= Y_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from ((( select distinct ei_id as EI= ID from mpng2_ei_attribute as reqin1 where reqin1.CATEGORYATTR_ID =3D 0 AND= reqin1.TEXT_VALUE ilike '' and ei_id in ( select distinct ei_id as EIID fr= om mpng2_ei_attribute as reqin2 where reqin2.CATEGORYATTR_ID =3D 0 AND reqi= n2.TEXT_VALUE ilike '' and ei_id in ( select distinct ei_id as EIID from mp= ng2_ei_attribute as reqin3 where reqin3.NAME =3D '' AND reqin3.STRING_VALUE= =3D '' ) ) ) ) ) as req0 join mpng2_ei_attribute on req0.eiid =3D mpng2_ei= _attribute.ei_id order by ei_id asc; QUERY PLAN=20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------- Unique (cost=3D758.53..762.19 rows=3D122 width=3D233) (actual time=3D0.191..0.191 rows=3D0 loops=3D1) -> Sort (cost=3D758.53..758.84 rows=3D122 width=3D233) (actual time=3D0.182..0.182 rows=3D0 loops=3D1) Sort Key: mpng2_ei_attribute.ei_id, mpng2_ei_attribute.integer_value, mpng2_ei_attribute.date_value, mpng2_ei_attribute.value_type, mpng2_ei_attribute.float_value, mpng2_ei_attribute.id, mpng2_ei_attribute.text_value, mpng2_ei_attribute.category_id, mpng2_ei_attribute.string_value, mpng2_ei_attribute.categoryattr_id, mpng2_ei_attribute.name -> Nested Loop (cost=3D365.83..754.31 rows=3D122 width=3D233) (actual time=3D0.126..0.126 rows=3D0 loops=3D1) -> Unique (cost=3D365.83..374.34 rows=3D1 width=3D4) (actu= al time=3D0.116..0.116 rows=3D0 loops=3D1) -> Nested Loop (cost=3D365.83..374.34 rows=3D1 width=3D4) (actual time=3D0.108..0.108 rows=3D0 loops=3D1) -> Unique (cost=3D350.22..354.69 rows=3D1 width=3D4) (actual time=3D0.097..0.097 rows=3D0 loops=3D1) -> Nested Loop (cost=3D350.22..354.69 rows=3D1 width=3D4) (actual time=3D0.089..0.089 rows=3D0 loops=3D1) -> Unique (cost=3D334.60..335.03 rows=3D1 width=3D4) (actual time=3D0.080..0.080 rows=3D0 loops=3D1) -> Sort (cost=3D334.60..334.82 rows=3D86 width=3D4) (actual time=3D0.072..0.072 row= s=3D0 loops=3D1) Sort Key: reqin3.ei_id -> Bitmap Heap Scan on mpng2_ei_attribute reqin3 (cost=3D2.52..331.84 rows=3D86 width=3D4) (actual time=3D0.056..0.056 rows=3D0 loops=3D1) Recheck Cond: (((name)::text =3D ''::text) AND ((string_value)::text =3D ''::text)) -> Bitmap Index Scan on mpng2_ei_attribute_name_svalue (cost=3D0.00..2.52 rows=3D86 width=3D0) (actual time=3D0.043..0.043 rows=3D0 loops=3D1) Index Cond: (((name)::text =3D ''::text) AND ((string_value)::text =3D ''::text)) -> Bitmap Heap Scan on mpng2_ei_attribute reqin2 (cost=3D15.61..19.63 rows=3D1 width=3D4) (never executed) Recheck Cond: ((reqin2.ei_id =3D "outer".ei_id) AND (reqin2.categoryattr_id =3D 0)) Filter: (text_value ~~* ''::text) -> BitmapAnd (cost=3D15.61..15.61 rows=3D1 width=3D0) (never executed) -> Bitmap Index Scan on mpng2_ei_attribute_ei_id (cost=3D0.00..2.43 rows=3D122 width=3D0) (neve= r executed) Index Cond: (reqin2.ei_id =3D "outer".ei_id) -> Bitmap Index Scan on mpng2_ei_attribute_categoryattr (cost=3D0.00..12.94 rows=3D1982 width= =3D0) (never executed) Index Cond: (categoryattr_id =3D 0) -> Bitmap Heap Scan on mpng2_ei_attribute reqin1 (cost=3D15.61..19.63 rows=3D1 width=3D4) (never executed) Recheck Cond: ((reqin1.ei_id =3D "outer".ei_id) AND (reqin1.categoryattr_id =3D 0)) Filter: (text_value ~~* ''::text) -> BitmapAnd (cost=3D15.61..15.61 rows=3D1 width=3D0) (never executed) -> Bitmap Index Scan on mpng2_ei_attribute_ei_id (cost=3D0.00..2.43 rows=3D122 width=3D0) (never executed) Index Cond: (reqin1.ei_id =3D "outer".ei_id) -> Bitmap Index Scan on mpng2_ei_attribute_categoryattr (cost=3D0.00..12.94 rows=3D1982 width=3D0) (never executed) Index Cond: (categoryattr_id =3D 0) -> Index Scan using mpng2_ei_attribute_ei_id on mpng2_ei_attribute (cost=3D0.00..378.43 rows=3D122 width=3D233) (never executed) Index Cond: ("outer".ei_id =3D mpng2_ei_attribute.ei_id) Thanks, --=20 J=C3=A9r=C3=B4me, python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for p in 'sioneb@gnireenigne-aigra.rf'.split('@')])" --=-i1ZUz+7ZHEqSxbosslF0 Content-Type: application/pgp-signature; name=signature.asc Content-Description: Ceci est une partie de message =?ISO-8859-1?Q?num=E9riquement?= =?ISO-8859-1?Q?_sign=E9e?= -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2.2 (GNU/Linux) iD8DBQBFCWa/CDKlmlhpwnERAqcvAJ43LJONAS44R4nYQZrN9MINaGP3+gCfTD/c iyXMW2yTQigoOTxs5TsibTg= =8XA7 -----END PGP SIGNATURE----- --=-i1ZUz+7ZHEqSxbosslF0--