Thread: index usage

index usage

From
Ben
Date:
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....?

Re: index usage

From
"Daniel Caune"
Date:
> De : pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
> owner@postgresql.org] De la part de Ben
> Envoyé : vendredi, juillet 28, 2006 15:21
> À : pgsql-performance@postgresql.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

Re: index usage

From
Ben
Date:
It's volatile, but it will always return an integer.

On Fri, 28 Jul 2006, Daniel Caune wrote:

>> De : pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
>> owner@postgresql.org] De la part de Ben
>> Envoyé : vendredi, juillet 28, 2006 15:21
>> À : pgsql-performance@postgresql.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
>

Re: index usage

From
Mark Lewis
Date:
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@postgresql.org [mailto:pgsql-performance-
> >> owner@postgresql.org] De la part de Ben
> >> Envoyé : vendredi, juillet 28, 2006 15:21
> >> À : pgsql-performance@postgresql.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

Re: index usage

From
Tom Lane
Date:
Ben <bench@silentmedia.com> writes:
> It's volatile, but it will always return an integer.

If it's volatile then it can't be used for an index condition.

            regards, tom lane