DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:disposition-notification-to:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=jbPYM38Ay8FVtQmQDvksT+B698FpXgWhXpHF47BjLLD3zuAmo3/VrWtf3WziROgGxC+nABxqCv+yZJOW5qojPzf7/hVJ5q/ZDExMtyW7NqWwy3KlkiXflzKPiJNeI9lBlKiUcKo1xt/CP6y1o5nSadfZoVr1vQQRKR+/9LFikwA=
Message-ID: <4716C3F2.5040206@gmail.com>
Date: Thu, 18 Oct 2007 10:24:50 +0800
From: =?UTF-8?B?5p2O5b2mIElhbiBMaQ==?= <liyan82@gmail.com>
User-Agent: Mozilla-Thunderbird 2.0.0.6 (X11/20071009)
MIME-Version: 1.0
To: Stefano Dal Pra <s.dalpra@gmail.com>
CC: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: two queryes in a single tablescan
References: <290a5abc0710170530v215cb2b0q4fa000ad43096a28@mail.gmail.com>
In-Reply-To: <290a5abc0710170530v215cb2b0q4fa000ad43096a28@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

I remember when I was using SQL server we did like like that:

SELECT count(CASE WHEN A THEN 1 END) AS cnt_a, count(CASE WHEN B 
THEN 1 END) AS cnt_b FROM tab WHERE C;

I did a little test with pg_bench data, also works in PostgreSQL:

test=# select count(*) from history where tid = 1;
  count
-------
    574
(1 行)

时间: 9.553 ms
test=# select count(*) from history where tid = 2;
  count
-------
   1107
(1 行)

时间: 8.949 ms
test=# select count(CASE WHEN tid = 1 then 1 END) as t1_cont, 
count(case when tid=2 then 1 end) as t2_cnt from history ;
  t1_cont | t2_cnt
---------+--------
      574 |   1107
(1 行)

时间: 17.182 ms

Hope that helps.

Regards

Stefano Dal Pra wrote:
> Hi everybody,
> 
> suppose you have a large table tab and two (or more) queryes like this:
> 
> SELECT count(*),A FROM tab WHERE C GROUP BY A;
> SELECT count(*),B FROM tab WHERE C GROUP BY B;
> 
> is there any way to get both results in a single query,
> eventually through stored procedure?
> The retrieved [count(*),A] ; [count(*),B)] data couldnt fit
> on a single table, of course.
> 
> The main goal would be to get multiple results while scanning the
> table[s] once only
> thus getting results in a faster  way.
> 
> This seems to me quite a common situation but i have no clue whether a neat
> solution can be implemented through stored procedure.
> 
> Any hint?
> 
> Thank you
> 
> Stefano