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=P4g9ICS3Q6g0+IX44+pd3ahGjV6tDepFTN8iFu3u2cJ4g+JyjiszzaZfjmHUMBETeA
	kX8A+ew1hccOQevEQvZWG0u/f6Wz1IglJnV8uSNUsdMPTvi8Srf04kYMS/C9k1ZRVEK7
	eNbv5H4Vq5bvJEFduGX08fFeKCR4x+DNITCy4=
Message-ID: <4AEA0DCD.7070704@gmail.com>
Date: Thu, 29 Oct 2009 15:49:01 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Grant Masan <grant.massan@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: slow count(CASE) query
References: <c09b5d640910291127k6a49d3a8w91a85cf69640bb3@mail.gmail.com>
In-Reply-To: <c09b5d640910291127k6a49d3a8w91a85cf69640bb3@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit


Grant Masan wrote:
> Hi all, 
> 
> I have this kind of query that I need to do, yes my query is giving
> right answers now but it is long and slow. I am now asking you that if 
> you have another solution for my query to make that more smarter ! Hope
> you can help me with this ! 
> 
> 
> select '000100' as length, sum(ship1) as ship1 ,sum(ship2) as
> ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM 
> (select 
> count(CASE WHEN (type between 40 and 49)  THEN 1 ELSE NULL END) as ship1, 
> count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, 
> count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, 
> count(CASE WHEN (type between 80 and 89)  THEN 1 ELSE NULL END) as ship4, 
> count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 
> FROM school_proj_boat where length <100 
> GROUP BY type 
> ORDER BY type) as koo 
> 
> UNION ALL 
> 
> select '100200' as length, sum(ship1) as ship1 ,sum(ship2) as
> ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM 
> (select 
> count(CASE WHEN (type between 40 and 49)  THEN 1 ELSE NULL END) as ship1, 
> count(CASE  WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, 
> count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, 
> count(CASE  WHEN (type between 80 and 89)  THEN 1 ELSE NULL END) as ship4, 
> count(CASE  WHEN (type >90) THEN 1 ELSE NULL END) as ship5 
> FROM school_proj_boat where length between 100 and 200 
> GROUP BY type 
> ORDER BY type) as koo 
> 
> UNION ALL 
> 
> select '200300' as length, sum(ship1) as ship1 ,sum(ship2) as
> ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM 
> (select 
> count(CASE WHEN (type between 40 and 49)  THEN 1 ELSE NULL END) as ship1, 
> count(CASE  WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, 
> count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, 
> count(CASE  WHEN (type between 80 and 89)  THEN 1 ELSE NULL END) as ship4, 
> count(CASE  WHEN (type >90) THEN 1 ELSE NULL END) as ship5 
> FROM school_proj_boat where length between 200 and 300 
> GROUP BY type 
> ORDER BY type) as koo 
> 
> UNION ALL 
> 
> select '300999' as length, sum(ship1) as ship1 ,sum(ship2) as
> ship2,sum(ship3) as ship3,sum(ship4) as ship4,sum(ship5) as ship5 FROM 
> (select 
> count(CASE WHEN (type between 40 and 49)  THEN 1 ELSE NULL END) as ship1, 
> count(CASE  WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, 
> count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, 
> count(CASE  WHEN (type between 80 and 89)  THEN 1 ELSE NULL END) as ship4, 
> count(CASE  WHEN (type >90) THEN 1 ELSE NULL END) as ship5 
> FROM school_proj_boat where length >300 
> GROUP BY type 
> ORDER BY type) as koo


Not thrilled with the "coded" length in the returned record but you
could CASE that too and then you would only read the data once.

If you need the explicit length range in the final result you could do

select mod(length,100) * 100 as low_length, (mod(length,100) + 1) * 100
as high_length....
group by low_length, high_length, type

You would of course get more type of records 400-500, 500-600 etc but
again it all happens in a single read.

And temp tables can be your friend too!