From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: Grant Masan <grant.massan@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: slow count(CASE) query
Date: Thu, 29 Oct 2009 15:14:04 -0300
Message-ID: <4AE9DB6C.6000203@fmed.uba.ar> (raw)
In-Reply-To: <c09b5d640910291127k6a49d3a8w91a85cf69640bb3@mail.gmail.com>
References: <c09b5d640910291127k6a49d3a8w91a85cf69640bb3@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gherzig@fmed.uba.ar, grant.massan@gmail.com
Subject: Re: slow count(CASE) query
In-Reply-To: <4AE9DB6C.6000203@fmed.uba.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox