Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 32E476331CD
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 21 Oct 2009 11:55:31 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 42060-10 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 21 Oct 2009 14:55:23 +0000 (UTC)
X-Greylist: delayed 00:06:27.505751 by SQLgrey-1.7.6
Received: from mail-pw0-f44.google.com (mail-pw0-f44.google.com
	[209.85.160.44])
	by mail.postgresql.org (Postfix) with ESMTP id 1B160632F76
	for <pgsql-sql@postgresql.org>; Wed, 21 Oct 2009 11:55:22 -0300 (ADT)
Received: by pwj1 with SMTP id 1so1197814pwj.3
	for <pgsql-sql@postgresql.org>; Wed, 21 Oct 2009 07:55:21 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from
	:user-agent:mime-version:to:cc:subject:references:in-reply-to
	:content-type:content-transfer-encoding;
	bh=03JdFlFVs7toQjb8vUwqcmtskvBBHOvZd9+k21Byaik=;
	b=lLnv9QhTQpEj1Iyvu0Stmki4AZyc7NteKlhTS5tx4zQkjdYnh/s6va1iNV8T/pxtcA
	ugArqwLweAKtU63gFHXOhrfPe5uxdMlte3cS1alGp9NFrAdcs8XAJJoFKOf+9b7YYwFJ
	BZR0mAirW0772kr5rWHSLggmynhq5+pS+6uf4=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=dQ9iAHtOQLUlYlfwJGAcVs9jjfOW34PQXdY8jA+zL0tVZitdUgMIS+SQwJo0J5rbsx
	4Dhy1mrsogTs2IzLzRfAhppy1eEOzr/U/7VsKUVR/uoidxjISi6fOxXn+tYNqFj6L6Dd
	jdH2JsVqzNw9NKQrCdSfvyb9OqX7g+js9uIoU=
Received: by 10.114.18.29 with SMTP id 29mr11881171war.147.1256136533788;
	Wed, 21 Oct 2009 07:48:53 -0700 (PDT)
Received: from ?172.16.1.239? ([208.187.158.94])
	by mx.google.com with ESMTPS id 22sm779408pzk.14.2009.10.21.07.48.52
	(version=TLSv1/SSLv3 cipher=RC4-MD5);
	Wed, 21 Oct 2009 07:48:53 -0700 (PDT)
Message-ID: <4ADF1F4C.8050509@gmail.com>
Date: Wed, 21 Oct 2009 08:48:44 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
CC: pgsql-sql@postgresql.org
Subject: Re: report generation from table.
References: <f966c2ee0910210452x4a60093ds772b62063e72fe8c@mail.gmail.com>
	<20091021121607.GA1086@a-kretschmer.de>
In-Reply-To: <20091021121607.GA1086@a-kretschmer.de>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200910/87
X-Sequence-Number: 33286



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.