Re: Baffled by failure to use index when WHERE uses a function

On Fri, Mar 10, 2006 at 09:14:27AM +0000, Clive Page wrote:
> I have a table cov3 of about 3 million rows, with a B-tree index on an
> integer column called hpix.  If I do a simple select on this column it
> works in milliseconds, using the index naturally:

<snip>

> So I would like to use this function to find rows, and I try for example:
>
> select * from cov3 where hpix = healpix(2.85,-11.48);
>
> but it takes ages.  An EXPLAIN shows why, it insists upon a sequential
> scan:

<snip>

You don't describe the exact structure of your table nor the exact
declaraion of your function, but is it possible your function is marked
VOLATILE rather tha STABLE or IMMUTABLE?

> I am using Postgres 8.1.0 on Linux.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.