Skip site navigation (1) Skip section navigation (2)

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

From: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
To:
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Should the optimiser convert a CASE into a WHERE if it can?
Date: 2010-01-26 17:41:32
Message-ID: 4B5F294C.3030803@cam.ac.uk (view raw or flat)
Thread:
Lists: pgsql-performance
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(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.
> 

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

In response to

Responses

pgsql-performance by date

Next:From: Viji V NairDate: 2010-01-26 18:23:25
Subject: Re: splitting data into multiple tables
Previous:From: Greg SmithDate: 2010-01-26 17:41:06
Subject: Re: splitting data into multiple tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group