From: Richard Huxton <dev@archonet.com>
To: Grant Masan <grant.massan@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: slow count(CASE) query
Date: Fri, 30 Oct 2009 08:40:36 +0000
Message-ID: <4AEAA684.60905@archonet.com> (raw)
In-Reply-To: <c09b5d640910291127k6a49d3a8w91a85cf69640bb3@mail.gmail.com>
References: <c09b5d640910291127k6a49d3a8w91a85cf69640bb3@mail.gmail.com>
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
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: dev@archonet.com, grant.massan@gmail.com
Subject: Re: slow count(CASE) query
In-Reply-To: <4AEAA684.60905@archonet.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