slow count(CASE) query - Mailing list pgsql-sql
| From | Grant Masan |
|---|---|
| Subject | slow count(CASE) query |
| Date | |
| Msg-id | c09b5d640910291127k6a49d3a8w91a85cf69640bb3@mail.gmail.com Whole thread Raw |
| Responses |
Re: slow count(CASE) query
(Gerardo Herzig <gherzig@fmed.uba.ar>)
Re: slow count(CASE) query (Rob Sargent <robjsargent@gmail.com>) Re: slow count(CASE) query (Richard Huxton <dev@archonet.com>) |
| List | pgsql-sql |
<span class="Apple-style-span" style="font-family: verdana, geneva, helvetica, arial, sans-serif; font-size: 13px; border-collapse:collapse; ">Hi all, <br /><br />I have this kind of query that I need to do, yes my query is giving rightanswers now but it is long and slow. I am now asking you that if <br /> you have another solution for my query to makethat more smarter ! Hope you can help me with this ! <br /><br /><br />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 <br /> (select <br />count(CASEWHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, <br />count(CASE WHEN (type between 60 and 69)THEN 1 ELSE NULL END) as ship2, <br />count(CASE WHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, <br />count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, <br />count(CASE WHEN (type >90) THEN 1 ELSENULL END) as ship5 <br />FROM school_proj_boat where length <100 <br />GROUP BY type <br />ORDER BY type) as koo <br/><br />UNION ALL <br /><br />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 <br />(select <br />count(CASE WHEN (type between 40 and 49) THEN 1 ELSENULL END) as ship1, <br /> count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, <br />count(CASEWHEN (type between 70 and 79) THEN 1 ELSE NULL END) as ship3, <br />count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, <br /> count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 <br />FROM school_proj_boatwhere length between 100 and 200 <br />GROUP BY type <br />ORDER BY type) as koo <br /><br />UNION ALL <br/><br />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 <br /> (select <br />count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, <br/>count(CASE WHEN (type between 60 and 69) THEN 1 ELSE NULL END) as ship2, <br />count(CASE WHEN (type between70 and 79) THEN 1 ELSE NULL END) as ship3, <br /> count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END)as ship4, <br />count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 <br />FROM school_proj_boat where lengthbetween 200 and 300 <br />GROUP BY type <br /> ORDER BY type) as koo <br /><br />UNION ALL <br /><br />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 <br/>(select <br />count(CASE WHEN (type between 40 and 49) THEN 1 ELSE NULL END) as ship1, <br /> count(CASE WHEN(type between 60 and 69) THEN 1 ELSE NULL END) as ship2, <br />count(CASE WHEN (type between 70 and 79) THEN 1 ELSENULL END) as ship3, <br />count(CASE WHEN (type between 80 and 89) THEN 1 ELSE NULL END) as ship4, <br /> count(CASE WHEN (type >90) THEN 1 ELSE NULL END) as ship5 <br />FROM school_proj_boat where length >300 <br />GROUPBY type <br />ORDER BY type) as koo</span>