2010/6/8 Kalai R <softlinne.kv@xxxxxxxxx>
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,11When 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.ThanksGloier
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
