Re: index usage

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
>
>From pgsql-performance-owner(at)postgresql(dot)org Fri Jul 28 18:02:18 2006
X-Original-To: pgsql-performance-postgresql(dot)org(at)postgresql(dot)org
Received: from localhost (mx1.hub.org [200.46.208.251])
by postgresql.org (Postfix) with ESMTP id 15F6A9FB270
for <pgsql-performance-postgresql(dot)org(at)postgresql(dot)org>; Fri, 28 Jul 2006 18:02:18 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
with ESMTP id 88817-04 for <pgsql-performance-postgresql(dot)org(at)postgresql(dot)org>;
Fri, 28 Jul 2006 18:02:02 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mir3-fs.mir3.com (mail.mir3.com [65.208.188.100])
by postgresql.org (Postfix) with ESMTP id BCD2D9FB27A
for <pgsql-performance(at)postgresql(dot)org>; Fri, 28 Jul 2006 18:01:21 -0300 (ADT)
Received: mir3-fs.mir3.com 172.16.1.11 from 172.16.2.68 172.16.2.68 via HTTP with MS-WebStorage 6.0.6249
Received: from archimedes.mirlogic.com by mir3-fs.mir3.com; 28 Jul 2006 14:01:18 -0700
Subject: Re: index usage
From: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>
To: Ben <bench(at)silentmedia(dot)com>
Cc: Daniel Caune <daniel(dot)caune(at)ubisoft(dot)com>, pgsql-performance(at)postgresql(dot)org
In-Reply-To: <Pine(dot)LNX(dot)4(dot)64(dot)0607281359030(dot)11400(at)GRD(dot)cube42(dot)tai(dot)silentmedia(dot)com>
References: <1E293D3FF63A3740B10AD5AAD88535D202B655F1(at)UBIMAIL1(dot)ubisoft(dot)org>
<Pine(dot)LNX(dot)4(dot)64(dot)0607281359030(dot)11400(at)GRD(dot)cube42(dot)tai(dot)silentmedia(dot)com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Organization: MIR3, Inc.
Date: Fri, 28 Jul 2006 14:01:18 -0700
Message-Id: <1154120478(dot)1634(dot)666(dot)camel(at)archimedes>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 (2.0.2-27)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.061 tagged_above=0 required=5 tests=AWL,
UNPARSEABLE_RELAY
X-Spam-Level:
X-Archive-Number: 200607/274
X-Sequence-Number: 20134

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.
>=20
> On Fri, 28 Jul 2006, Daniel Caune wrote:
>=20
> >> De : pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-
> >> owner(at)postgresql(dot)org] De la part de Ben
> >> Envoy=C3=A9 : vendredi, juillet 28, 2006 15:21
> >> =C3=80 : pgsql-performance(at)postgresql(dot)org
> >> Objet : [PERFORM] index usage
> >>
> >> I have a table with 37000 rows, an integer column, and an index on tha=
t
> >> column. I've got a function that returns an integer. When I do a selec=
t
> >> where I restrict that column to being equal to a static number, explai=
n
> >> 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 t=
he
> >> table.
> >>
> >> I must be missing something, because my understanding is that the func=
tion
> >> will be evaluated once for the statement and then collapsed into a sta=
tic
> >> 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