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

Thanks for your answers.


David Wilson wrote:

 > Why not simply add the where clause to the original query?
 >
 > 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,
 > SUM (case when id > 1220000 and id < 1230000 then 1 else 0 end) AS c3,
 > SUM (case when id > 1230000 and id < 1240000 then 1 else 0 end) AS c4,
 > SUM (case when id > 1240000 and id < 1250000 then 1 else 0 end) AS c5
 > FROM tbl_tracker WHERE (id>1200000) AND (id<1250000);
 >
 > I didn't populate any test tables, but I'd expect that to do just as
 > well without being any uglier than the original query is.

You're absolutely right, but I'm afraid this won't help. I'd simplified
the original example query, but in real life, I've got about 50
different sub-ranges, which cover virtually all the id-space.

----------

Tom Lane wrote:
> 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.
>

OK - that's all I was wondering. I thought I'd raise this in case it
might be helpful.

I'll add a note to:
http://www.postgresql.org/docs/8.4/interactive/functions-conditional.html
to point out that this is something of a trap for the unwary

Regards,

Richard