pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: slow count(CASE) query
4+ messages / 4 participants
[nested] [flat]
* slow count(CASE) query
@ 2009-10-29 18:27 Grant Masan <grant.massan@gmail.com>
2009-10-29 18:14 ` Re: slow count(CASE) query Gerardo Herzig <gherzig@fmed.uba.ar>
2009-10-29 21:49 ` Re: slow count(CASE) query Rob Sargent <robjsargent@gmail.com>
2009-10-30 08:40 ` Re: slow count(CASE) query Richard Huxton <dev@archonet.com>
0 siblings, 3 replies; 4+ messages in thread
From: Grant Masan @ 2009-10-29 18:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: slow count(CASE) query
2009-10-29 18:27 slow count(CASE) query Grant Masan <grant.massan@gmail.com>
@ 2009-10-29 18:14 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2 siblings, 0 replies; 4+ messages in thread
From: Gerardo Herzig @ 2009-10-29 18:14 UTC (permalink / raw)
To: Grant Masan <grant.massan@gmail.com>; +Cc: 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 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: slow count(CASE) query
2009-10-29 18:27 slow count(CASE) query Grant Masan <grant.massan@gmail.com>
@ 2009-10-29 21:49 ` Rob Sargent <robjsargent@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Rob Sargent @ 2009-10-29 21:49 UTC (permalink / raw)
To: Grant Masan <grant.massan@gmail.com>; +Cc: 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
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!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: slow count(CASE) query
2009-10-29 18:27 slow count(CASE) query Grant Masan <grant.massan@gmail.com>
@ 2009-10-30 08:40 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2009-10-30 08:40 UTC (permalink / raw)
To: Grant Masan <grant.massan@gmail.com>; +Cc: pgsql-sql
Grant Masan wrote:
>
> 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
...
> FROM school_proj_boat where length between 100 and 200
> GROUP BY type
> ORDER BY type) as koo
>
> UNION ALL
...
First thing is to ditch the UNION ALLs. You're basically repeating the
same query.
Create a lookup table: length_codes (code, min_length, max_length)
Data: ('100100', 0, 99), ('100200', 100, 199), ...
SELECT length_code AS length, sum...
FROM (
SELECT
lc.code AS length_code,
count(case)...
FROM
school_proj_boat spb, length_codes lc
WHERE
spb.length BETWEEN lc.min_length AND lc.max_length
) AS koo
;
It's easy to forget that you can join against a table using any
condition, it doesn't have to be equality. Here we use BETWEEN to
replace our UNIONs.
You'll want a unique constraint on length_codes.code and you should
really write a custom trigger to make sure none of the
min_length..max_length ranges overlap. In practice, you're probably only
setting this table up once so might not bother.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-10-30 08:40 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-29 18:27 slow count(CASE) query Grant Masan <grant.massan@gmail.com>
2009-10-29 18:14 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2009-10-29 21:49 ` Rob Sargent <robjsargent@gmail.com>
2009-10-30 08:40 ` Richard Huxton <dev@archonet.com>
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