Re: index usage

[Mark Lewis <mark(dot)lewis(at)mir3(dot)com>]

A volatile function has may return a different result for each row;
think of the random() or nextval() functions for example.  You wouldn't
want them to return the same value for each row returned.

-- Mark Lewis

On Fri, 2006-07-28 at 13:59 -0700, Ben wrote:
> It's volatile, but it will always return an integer.
> 
> On Fri, 28 Jul 2006, Daniel Caune wrote:
> 
> >> De : pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-
> >> owner(at)postgresql(dot)org] De la part de Ben
> >> Envoyà : vendredi, juillet 28, 2006 15:21
> >> Ã : pgsql-performance(at)postgresql(dot)org
> >> Objet : [PERFORM] index usage
> >>
> >> I have a table with 37000 rows, an integer column, and an index on that
> >> column. I've got a function that returns an integer. When I do a select
> >> where I restrict that column to being equal to a static number, explain
> >> tells me the index will be used. When I do the same thing but use the
> >> function instead of a static number, explain shows me a full scan on the
> >> table.
> >>
> >> I must be missing something, because my understanding is that the function
> >> will be evaluated once for the statement and then collapsed into a static
> >> number for the filtering. But the results of the explain seem to imply
> >> that's not the case....?
> >>
> >
> > Is your function IMMUTABLE, STABLE or VOLATILE?
> >
> > --
> > Daniel
> >
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings