Subject: Re: High CPU Load
From: =?ISO-8859-1?Q?J=E9r=F4me?= BENOIS <benois@argia-engineering.fr>
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: Dave Dutcher <dave@tridecap.com>,
	pgsql-performance@postgresql.org
In-Reply-To: <1158249411.24726.17.camel@state.g2switchworks.com>
References: <004001c6d80e$bdef5bc0$8300a8c0@tridecap.com>
	<1158249411.24726.17.camel@state.g2switchworks.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-2VLVWJUDT2+njysVIffz"
Date: Thu, 14 Sep 2006 23:07:37 +0200
Message-Id: <1158268058.5167.2.camel@localhost.localdomain>
Mime-Version: 1.0


--=-2VLVWJUDT2+njysVIffz
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hi Scott,

Le jeudi 14 septembre 2006 =C3=A0 10:56 -0500, Scott Marlowe a =C3=A9crit :
> On Thu, 2006-09-14 at 10:02, Dave Dutcher wrote:
> > > -----Original Message-----
> > > From: pgsql-performance-owner@postgresql.org=20
> > > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20
> > > J=C3=A9r=C3=B4me BENOIS
> > >=20
> >  explain analyze select distinct
> > > INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_
> > > VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from (((=20
> > > select distinct ei_id as EIID from mpng2_ei_attribute as=20
> > > reqin1 where reqin1.CATEGORYATTR_ID =3D 0 AND reqin1.TEXT_VALUE=20
> > > ilike '' and ei_id in ( select distinct ei_id as EIID from=20
> > > mpng2_ei_attribute as reqin2 where reqin2.CATEGORYATTR_ID =3D 0=20
> > > AND reqin2.TEXT_VALUE ilike '' and ei_id in ( select distinct=20
> > > ei_id as EIID from mpng2_ei_attribute as reqin3 where=20
> > > reqin3.NAME =3D '' AND reqin3.STRING_VALUE =3D '' ) ) ) ) ) as=20
> > > req0 join mpng2_ei_attribute on req0.eiid =3D=20
> > > mpng2_ei_attribute.ei_id order by ei_id asc;
> >=20
> >=20
> > That is a lot of distinct's.  Sorts are one thing that can really use u=
p
> > CPU.  This query is doing lots of sorts, so its not surprising the CPU =
usage
> > is high. =20
>=20
> I'm gonna make a SWAG here and guess that maybe your 7.4 db was initdb'd
> with a locale of C and the new one is initdb'd with a real locale, like
> en_US.  Can J=C3=A9r=C3=B4me confirm or deny this?
>=20

The locale used to run initdb is :

su - postgres
:~$ locale
LANG=3DPOSIX
LC_CTYPE=3D"POSIX"
LC_NUMERIC=3D"POSIX"
LC_TIME=3D"POSIX"
LC_COLLATE=3D"POSIX"
LC_MONETARY=3D"POSIX"
LC_MESSAGES=3D"POSIX"
LC_PAPER=3D"POSIX"
LC_NAME=3D"POSIX"
LC_ADDRESS=3D"POSIX"
LC_TELEPHONE=3D"POSIX"
LC_MEASUREMENT=3D"POSIX"
LC_IDENTIFICATION=3D"POSIX"
LC_ALL=3D

Cheers,
--=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('@')])"

--=-2VLVWJUDT2+njysVIffz
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)

iD8DBQBFCcSZCDKlmlhpwnERAu3KAJ4vrFTIxwUdxo0eMIy7OkFlXHZ3zQCdEESN
9MErEd5moK00n9uIojMQD/k=
=MQVV
-----END PGP SIGNATURE-----

--=-2VLVWJUDT2+njysVIffz--