Postgres Pro
Downloads
Home   >   mailing lists

Postgres not using index on views - Mailing list pgsql-performance

From David G. Johnston
Subject Postgres not using index on views
Date
Msg-id CAKFQuwa4OiNpgZ9brTCh1dsM-asK2mMY+Wm6xOE6_gM5UngQ0w@mail.gmail.com
Whole thread Raw
In response to RE: Postgres not using index on views  (Rick Vincent <rvincent@temenos.com>)
Responses RE: Postgres not using index on views
RE: Postgres not using index on views
List pgsql-performance
Tree view
On Friday, April 17, 2020, Rick Vincent <rvincent@temenos.com> wrote:
Hi,
 
I was wondering if anyone can explain the below problem.  Should a bug be logged for this?
 
Kind regards,
Rick
 
_____________________________________________
From: Rick Vincent
Sent: Tuesday, April 7, 2020 11:08 AM
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-performance@postgresql.org; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
Subject: RE: Postgres not using index on views
 
 
Hi Tom,
 
The function is defined as below, so no use of VOLATILE.  Let me know if you need any other information.  I am hoping the below will further clarify the issue.
 

IIUC as Tom wrote you have volatile functions (implied/default as Thomas wrote) attached to view column outputs and the planner will not optimize those away.

Mark your function immutable (assuming it is) and retry your experiment with the where clause query. 

David J.

pgsql-performance by date:

Previous
From: Rick Vincent
Date:
Subject: RE: Postgres not using index on views
Next
From: Rick Vincent
Date:
Subject: RE: Postgres not using index on views