> -----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 dont 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.