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

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

From: Scott Carey <scott(at)richrelevance(dot)com>
To: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Should the optimiser convert a CASE into a WHERE if it can?
Date: 2010-01-26 21:32:21
Message-ID: 01BAE464-C5D9-405B-89E3-1D76B1D4658D@richrelevance.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Jan 26, 2010, at 9:41 AM, Richard Neill wrote:

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

Well, it probably shouldn't use the index if it covers the vast majority of the table.  I wonder if it is actually faster to reformulate with WHERE or not at that point -- it might be slower.

In response to

pgsql-performance by date

Next:From: Greg SmithDate: 2010-01-26 21:53:21
Subject: Re: New server to improve performance on our large and busy DB - advice?
Previous:From: Scott CareyDate: 2010-01-26 21:27:24
Subject: Re: Inserting 8MB bytea: just 25% of disk perf used?

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