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
> 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
WHEN length(s_umbname) > 0 THEN s_umbname || '-' || s_res
END FROM tbl_cmpy WHERE id = $1;
$$ LANGUAGE sql STABLE STRICT;
I took off the limit 1 because you probably don't need it.
In response to
pgsql-bugs by date
|Next:||From: Arthur Nascimento||Date: 2011-03-23 19:09:19|
|Subject: BUG #5942: pg_trgm.sql has cyclic dependency on type gtrgm creation|
|Previous:||From: Donald Fraser||Date: 2011-03-23 13:10:28|
|Subject: Re: Index Ignored Due To Use Of View|