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
