Re: High CPU Load - Mailing list pgsql-performance

From Jérôme BENOIS
Subject Re: High CPU Load
Date
Msg-id 1158246565.5226.59.camel@localhost.localdomain
Whole thread Raw
In response to Re: High CPU Load  ("Dave Dutcher" <dave@tridecap.com>)
Responses Re: High CPU Load
List pgsql-performance
Hi Dave,
Le jeudi 14 septembre 2006 à 10:02 -0500, Dave Dutcher a écrit :
> > -----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.
>
> On the subqueries you have a couple of cases where you say "... in (select
> distinct ...)"  I don’t think the distinct clause is necessary in that case.
> I'm not a hundred percent sure, but you might want to try removing them and
> see if the query results are the same and maybe the query will execute
> faster.

Thanks for your advice, but the load was good with previous version of
postgres -> 7.4.6 on the same server and same datas, same application,
same final users ...

So we supect some system parameter, but which ?

With vmstat -s is showing a lot of "pages swapped out", have you an
idea ?

Thanls a lot,

--
Jérôme,

python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in 'sioneb@gnireenigne-aigra.rf'.split('@')])"

Attachment

pgsql-performance by date:

Previous
From: "Dave Dutcher"
Date:
Subject: Re: High CPU Load
Next
From: Francisco Reyes
Date:
Subject: Vacuums on large busy databases