Re: Query process is very slow

2010/6/8 Kalai R <softlinne(dot)kv(at)gmail(dot)com>

> Sir,
>
> I am using the following psql query with union and subqueries in my
> project.
>
>
> select a.areaname,f.fmno,fm.mno,f.aliasno as falias,f.salutation as
> fsal,f.headname, fm.aliasno as fmalias,fm.salutation as fmsal,fm.membername,
> extract (year from age(now(), to_date(fm.dob,'yyyy-MM-dd'))) as age,
> fr1.year,sum(fr2.amount) as amt from fmember fm left outer join fmreceipt2
> fr2 on fm.mno=fr2.mno, fmreceipt1 fr1,prayerarea a,family f where fr2.mno in
> (select fm2.mno from fmreceipt1 fm1,fmreceipt2 fm2 where fm1.refno =
> fm2.refno and fr1.edate>='01-06-2010' and fr1.edate<='08-06-2010' group by
> fm2.mno,fm1.year having (sum(fm2.amount)>=0 and sum(fm2.amount)<=1000))and a.areano=f.areano and fm.fmno=f.fmno and fm.dob<>'' and
> fr2.refno=fr1.refno group by
> a.areaname,f.fmno,fm.mno,f.aliasno,f.salutation,f.headname,fm.aliasno,
> fm.salutation,fm.membername,fm.dob,fr1.year,fr1.edate having extract (year
> from age(now(), to_date(fm.dob,'yyyy-MM-dd')))>0 and fr1.edate>='01-06-2010'
> and fr1.edate<='08-06-2010' union select
> a.areaname,f.fmno,fm.mno,f.aliasno as falias,f.salutation as fsal,
> f.headname,fm.aliasno as fmalias,fm.salutation as fmsal,fm.membername,
> extract (year from age(now(), to_date(fm.dob,'yyyy-MM-dd'))) as age,0,0 from
> fmember fm,family f,prayerarea a where fm.mno not in (select fm2.mno from
> fmreceipt2 fm2) and f.fmno=fm.fmno and a.areano=f.areano and fm.dob<>''
> group by a.areaname,f.fmno,fm.mno,f.aliasno,f.salutation,f.headname,
> fm.aliasno,fm.salutation,fm.membername,fm.dob having extract (year from
> age(now(), to_date(fm.dob,'yyyy-MM-dd')))>0 order by 1,2,3,11
>
>
> When I run this query it takes 1 minute to give result but in the receipt1
> table contains 4000 records only.
> I want to know what is the problem? Is there any mistake in the query?
>
> Kindly clarify.
>
> Thanks
> Gloier
>
>

Sorry, the query in this form is totally unreadable. First of all run
explain analyze and then paste the output here or use
http://explain.depesz.com/ and give us the link to the much
more readable version.

regards
Szymon Guz