From: Grant Masan <grant.massan@gmail.com>
To: pgsql-sql@postgresql.org
Subject: slow count(CASE) query
Date: Thu, 29 Oct 2009 20:27:33 +0200
Message-ID: <c09b5d640910291127k6a49d3a8w91a85cf69640bb3@mail.gmail.com> (raw)
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
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: grant.massan@gmail.com
Subject: Re: slow count(CASE) query
In-Reply-To: <c09b5d640910291127k6a49d3a8w91a85cf69640bb3@mail.gmail.com>
* 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