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

Re: Index Ignored Due To Use Of View

From: "Donald Fraser" <postgres(at)kiwi-fraser(dot)net>
To: "[BUGS]" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Index Ignored Due To Use Of View
Date: 2011-03-23 13:10:28
Message-ID: 479C9845FAF74274970CEF781EB9EF56@DEVELOP1 (view raw or flat)
Thread:
Lists: pgsql-bugs
----- 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.

Regards
Donald Fraser

Responses

pgsql-bugs by date

Next:From: Merlin MoncureDate: 2011-03-23 13:54:09
Subject: Re: Index Ignored Due To Use Of View
Previous:From: Donald FraserDate: 2011-03-23 12:50:19
Subject: Index Ignored Due To Use Of View

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