pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreport generation from table.
3+ messages / 3 participants
[nested] [flat]
* report generation from table.
@ 2009-10-21 11:52 sathiya psql <sathiya.psql@gmail.com>
2009-10-21 12:16 ` Re: report generation from table. A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 3+ messages in thread
From: sathiya psql @ 2009-10-21 11:52 UTC (permalink / raw)
To: pgsql-sql
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 ?
Or i dont have any other way ?!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: report generation from table.
2009-10-21 11:52 report generation from table. sathiya psql <sathiya.psql@gmail.com>
@ 2009-10-21 12:16 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-10-21 14:48 ` Re: report generation from table. Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: A. Kretschmer @ 2009-10-21 12:16 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: report generation from table.
2009-10-21 11:52 report generation from table. sathiya psql <sathiya.psql@gmail.com>
2009-10-21 12:16 ` Re: report generation from table. A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2009-10-21 14:48 ` Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Rob Sargent @ 2009-10-21 14:48 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-10-21 14:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-21 11:52 report generation from table. sathiya psql <sathiya.psql@gmail.com>
2009-10-21 12:16 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-10-21 14:48 ` Rob Sargent <robjsargent@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox