Re: slow count(CASE) query - Mailing list pgsql-sql

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 anested 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