2010/6/8 Kalai R <softlinne...@gmail.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
