Hi Dave, Le jeudi 14 septembre 2006 à 10:02 -0500, Dave Dutcher a écrit : > > -----Original Message----- > > From: pgsql-performance-owner@xxxxxxxxxxxxxx > > [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] 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@xxxxxxxxxxxxxxxxxxxx'.split('@')])"
Attachment:
signature.asc
Description: Ceci est une partie de message =?ISO-8859-1?Q?num=E9riquement?= =?ISO-8859-1?Q?_sign=E9e?=
