From: Dave Dutcher <dave@tridecap.com>
To: 'Jérôme 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> (raw)
In-Reply-To: <1158244032.5226.49.camel@localhost.localdomain>
> -----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.
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: dave@tridecap.com, benois@argia-engineering.fr
Subject: Re: High CPU Load
In-Reply-To: <004001c6d80e$bdef5bc0$8300a8c0@tridecap.com>
* 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