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

On Fri, 10 Mar 2006, Martijn van Oosterhout wrote:

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

Thanks for that hint - my function was not marked in any way, so I guess
it
got to be VOLATILE by default. I have just marked it as IMMUTABLE and it
now uses the index as expected, with a huge performance gain. I confess
that I was totally ignorant of the differences between these three types
of function.

Sorry I slightly messed up the cut/paste of my posting, I had been
experimenting with various versions of the same table and didn't quite get
the details consistent in what I posted.

Thanks also to Richard Huxton and Martin van Oosterhout who gave me the
same hint.

What an excellent support group this is.

--
Clive Page
Dept of Physics & Astronomy,
University of Leicester,
Leicester, LE1 7RH, U.K.