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:
>
> select * from cov3 where hpixint = 482787587;
                           ^^^^^^^
That's not the column you said you were using and that your other
example uses.  Have you verified that hpix has an index?

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

If healpix() always returns the same output for given input then
define it to be IMMUTABLE.  For more information see "Function
Volatility Categories" in the documentation.

http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html

--
Michael Fuhr