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

On Tue, 26 Jan 2010, Richard Neill wrote:
> SELECT SUM (case when id > 1200000 and id < 1210000 then 1 else 0 end) from
> tbl_tracker;
>
> Explain shows that this does a sequential scan.

I'd defer to Tom on this one, but really, for Postgres to work this out,
it would have to peer deep into the mysterious SUM function, and realise
that the number zero is a noop. I suppose it would be possible, but you'd
have to define noops for each of the different possible functions, *and*
make the planner clever enough to spot the noop-matching number in the
else and convert the WHEN into a WHERE.

In my mind, this is quite a lot of work for the planner to do to solve
this one. That translates into quite a lot of work for some poor
programmer to do to achieve it. If you have the money, then hire someone
to do it!

Matthew

--
I don't want the truth. I want something I can tell parliament!
-- Rt. Hon. Jim Hacker MP