Re: Index Ignored Due To Use Of View

On Wed, Mar 23, 2011 at 8:10 AM, Donald Fraser <postgres@kiwi-fraser.net> w=
rote:
> ----- Original Message -----
>
> Sent: Wednesday, March 23, 2011 12:50 PM
> Subject: Index Ignored Due To Use Of View
> PostgreSQL 8.3.14
> OS: Linux Redhat 5.4
>
> Note: I have used the same subject for this email taken from an email:
> Posted 2011-02-24 13:29:22-08 by "David Johnston", because this seems=A0t=
o be
> a very similar observation.
>
> Bug/Problem Summary:
> We are using a simple query based on a simple view and the query optimizer
> is not choosing an index.
> The same query without the view is using an index.
> The same query on an almost identical view, but having either removed a
> single column which was generated via a function call or replace the
> function call=A0with equivalent SQL, then=A0the query optimizer=A0is=A0ch=
oosing an
> index.
>
>
> I found the solution to the problem and it would therefore appear as thou=
gh
> this=A0is not a bug!
> If I change the function definition to be "STABLE" instead of "VOLATILE",
> then the problem goes away.

Also, it's bad practice to do order by/limit in the new definition
like that.  Leave them off, and do it in the calling query.  I would
write your function like this:

CREATE OR REPLACE FUNCTION get_cmpyname(integer)  RETURNS citext AS
$$
  SELECT CASE
    WHEN length(s_umbname) > 0 THEN s_umbname || '-' || s_res
    ELSE ''
  END FROM tbl_cmpy WHERE id =3D $1;
$$ LANGUAGE sql STABLE STRICT;

I took off the limit 1 because you probably don't need it.

merlin