slow count(CASE) query

[Gerardo Herzig]

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

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Massan]

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

[hazlup]

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!

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Richard Huxton]

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

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql