From: Jérôme BENOIS <benois@argia-engineering.fr>
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: Dave Dutcher <dave@tridecap.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: High CPU Load
Date: Thu, 14 Sep 2006 23:07:37 +0200
Message-ID: <1158268058.5167.2.camel@localhost.localdomain> (raw)
In-Reply-To: <1158249411.24726.17.camel@state.g2switchworks.com>
References: <004001c6d80e$bdef5bc0$8300a8c0@tridecap.com>
<1158249411.24726.17.camel@state.g2switchworks.com>
Hi Scott,
Le jeudi 14 septembre 2006 à 10:56 -0500, Scott Marlowe a écrit :
> On Thu, 2006-09-14 at 10:02, Dave Dutcher wrote:
> > > -----Original Message-----
> > > From: pgsql-performance-owner@postgresql.org
> > > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> > > Jérôme BENOIS
> > >
> > 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 (((
> > > select distinct ei_id as EIID from mpng2_ei_attribute as
> > > reqin1 where reqin1.CATEGORYATTR_ID = 0 AND reqin1.TEXT_VALUE
> > > ilike '' and ei_id in ( select distinct ei_id as EIID from
> > > mpng2_ei_attribute as reqin2 where reqin2.CATEGORYATTR_ID = 0
> > > AND reqin2.TEXT_VALUE ilike '' and ei_id in ( select distinct
> > > ei_id as EIID from mpng2_ei_attribute as reqin3 where
> > > reqin3.NAME = '' AND reqin3.STRING_VALUE = '' ) ) ) ) ) as
> > > req0 join mpng2_ei_attribute on req0.eiid =
> > > mpng2_ei_attribute.ei_id order by ei_id asc;
> >
> >
> > That is a lot of distinct's. Sorts are one thing that can really use up
> > CPU. This query is doing lots of sorts, so its not surprising the CPU usage
> > is high.
>
> 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érôme confirm or deny this?
>
The locale used to run initdb is :
su - postgres
:~$ locale
LANG=POSIX
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=
Cheers,
--
Jérôme,
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in 'sioneb@gnireenigne-aigra.rf'.split('@')])"
Attachments:
signature.asc (application/pgp-signature, 190B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: benois@argia-engineering.fr, smarlowe@g2switchworks.com, dave@tridecap.com
Subject: Re: High CPU Load
In-Reply-To: <1158268058.5167.2.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox