Обсуждение: report generation from table.
Hi All,<br /><br />I have been searching for, Preparing report from a huge table.<br /><br />Queries am executing now are,<br/>SELECT count(*) from HUGE_TBL where cond1, cond2;<br />SELECT count(*) from HUGE_TBL where cond1, cond3;<br /> ---like this i have different conditions(10-15 different things ) and different amount of conditions ( 2 - 5 condition ).<br/><br />As that is a very huge table, it took time to execute each query ( 2 min ). And finally it takes 15 times that( 30 min ). Is there anyway to do the above report efficiently ?<br /><br />Such executing the query only once, andgetting all different outputs required ? or fetching result from some cache ?<br />Or i dont have any other way ?!<br/>
In response to sathiya psql : > Hi All, > > I have been searching for, Preparing report from a huge table. > > Queries am executing now are, > SELECT count(*) from HUGE_TBL where cond1, cond2; > SELECT count(*) from HUGE_TBL where cond1, cond3; > --- like this i have different conditions(10-15 different things ) and > different amount of conditions ( 2 - 5 condition ). > > As that is a very huge table, it took time to execute each query ( 2 min ). And > finally it takes 15 times that ( 30 min ). Is there anyway to do the above > report efficiently ? > > Such executing the query only once, and getting all different outputs required > ? or fetching result from some cache ? select sum(case when <cond1> and <cond2> then 1 else 0 end) as query1, sum(case when <cond1> and <cond3> then 1 else 0 end) as query2, ... from table; It forces a whole seq. scan, but only once. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
A. Kretschmer wrote: > In response to sathiya psql : >> Hi All, >> >> I have been searching for, Preparing report from a huge table. >> >> Queries am executing now are, >> SELECT count(*) from HUGE_TBL where cond1, cond2; >> SELECT count(*) from HUGE_TBL where cond1, cond3; >> --- like this i have different conditions(10-15 different things ) and >> different amount of conditions ( 2 - 5 condition ). >> >> As that is a very huge table, it took time to execute each query ( 2 min ). And >> finally it takes 15 times that ( 30 min ). Is there anyway to do the above >> report efficiently ? >> >> Such executing the query only once, and getting all different outputs required >> ? or fetching result from some cache ? > > select sum(case when <cond1> and <cond2> then 1 else 0 end) as query1, > sum(case when <cond1> and <cond3> then 1 else 0 end) as query2, ... from > table; > > > It forces a whole seq. scan, but only once. > > > Regards, Andreas Probably should check (analyse) the various queries separately to see if any of them scan the huge table. If one does scan the table, then give Andreas's plan should be fine. However, it's possible that your conditions are all hitting indexes and not scanning, in which case you may be better off as is.