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

2010/1/26 Matthew Wakeling <matthew(at)flymine(dot)org>

> 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.
>
> Hello.

How about SELECT SUM (case when id > 1200000 and id < 1210000 then 1 end)
from tbl_tracker;
It gives same result (may be unless there are no records at all) and
optimizer already knows it need not to call function for null input. Such an
optimization would cover much more cases. It would look like:
* Check only for aggregate subselects
* All the functions should be noop for null input
* Add ORed constraint for every function input is not null (in this example
(case when id > A1 and id < B1 then 1 end is not null) or (case when id > A2
and id < B2 then 1 end is not null) or ... or (case when id > An and id < Bn
then 1 end is not null)
* Know special "case" (case when id > A1 and id < B1 then 1 end is not
null) <=> (id > A1 and id < B1)
by ORing all the "when" conditions case when C1 then D1 when C2 then D2 ...
when Cm then Dm end is not null <=> C1 or C2 or ... or Cm.
Event without last part it may give bonuses even for "select count(field)
from table" transformed into "select count(field) from table where field is
not null" and using [partial] indexes.
As of last "*", replacing COUNT with SUM(CASE()) is used often enough when
multiple count calculations are needed.

Best regards, Vitalii Tymchyshyn