Message-ID: <4AE9DB6C.6000203@fmed.uba.ar>
Date: Thu, 29 Oct 2009 15:14:04 -0300
From: Gerardo Herzig <gherzig@fmed.uba.ar>
User-Agent: Thunderbird 2.0.0.6 (X11/20070801)
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
> 
Well, it looks like this will read school_proj_boat 4 times.
What about

1) A plsql function that iterates *one time* on school_proj_boat, with a
 nested CASE, or a par of IF's

2) Could be a good place for using window functions
http://www.postgresql.org/docs/current/static/tutorial-window.html


HTH
Gerardo