Skip site navigation (1) Skip section navigation (2)

Re: Index Ignored Due To Use Of View

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Donald Fraser <postgres(at)kiwi-fraser(dot)net>
Cc: "[BUGS]" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Index Ignored Due To Use Of View
Date: 2011-03-23 13:54:09
Message-ID: AANLkTi=4Zizm=6EO7NighZO36e2Ahy9bHMPjrYH-1PL4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Arthur NascimentoDate: 2011-03-23 19:09:19
Subject: BUG #5942: pg_trgm.sql has cyclic dependency on type gtrgm creation
Previous:From: Donald FraserDate: 2011-03-23 13:10:28
Subject: Re: Index Ignored Due To Use Of View

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group