From: "Dave Dutcher" <dave@tridecap.com>
To: =?iso-8859-1?Q?'J=E9r=F4me_BENOIS'?= <benois@argia-engineering.fr>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: High CPU Load
Date: Thu, 14 Sep 2006 10:02:04 -0500
Message-ID: <004001c6d80e$bdef5bc0$8300a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <1158244032.5226.49.camel@localhost.localdomain>
Importance: Normal


> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org=20
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20
> J=E9r=F4me 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;


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. =20

On the subqueries you have a couple of cases where you say "... in =
(select
distinct ...)"  I don=92t 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.