Postgres not using index on views

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Friday, April 17, 2020, Rick Vincent <rvincent@xxxxxxxxxxx> 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@xxxxxxxxxxxxx>; Justin Pryzby <pryzby@xxxxxxxxxxxxx>
Cc: pgsql-performance@postgresql.org; Manoj Kumar <manojkumar@xxxxxxxxxxx>; Herve Aubert <haubert@xxxxxxxxxxx>
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.