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

Richard Neill <rn214@cam.ac.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