Re: Should the optimiser convert a CASE into a WHERE if it can?

Richard Neill <rn214(at)cam(dot)ac(dot)uk> writes:
> SELECT
> SUM (case when id > 1200000 and id < 1210000 then 1 else 0 end) AS c1,
> SUM (case when id > 1210000 and id < 1220000 then 1 else 0 end) AS c2,
> ...
> FROM tbl_tracker;

> This can be manually optimised into a far uglier (but much much faster)
> query:

> SELECT * FROM
> (SELECT COUNT (1) AS c1 FROM tbl_tracker
> WHERE id > 1200000 and id < 1210000) AS s1,
> (SELECT COUNT (1) AS c2 FROM tbl_tracker
> WHERE id > 1210000 and id < 1220000) AS s2,
> ...

We're unlikely to consider doing this, for a couple of reasons:
it's unlikely to come up often enough to justify the cycles the planner
would spend looking for the case *on every query*, and it requires very
special knowledge about the behavior of two specific aggregate functions,
which is something the planner tends to avoid using.

regards, tom lane