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

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

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
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:23:06
Message-ID: alpine.DEB.2.00.1001261715510.6195@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2010-01-26 17:41:06
Subject: Re: splitting data into multiple tables
Previous:From: Tom LaneDate: 2010-01-26 17:21:38
Subject: Re: Should the optimiser convert a CASE into a WHERE if it can?

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