Re: Index Ignored Due To Use Of View

On Wed, Mar 23, 2011 at 8:10 AM, Donald Fraser <postgres(at)kiwi-fraser(dot)net> wrote:
> ----- 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 to 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 with equivalent SQL, then the query optimizer is choosing an
> index.
>
>
> I found the solution to the problem and it would therefore appear as though
> this is 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 = $1;
$$ LANGUAGE sql STABLE STRICT;

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

merlin