Dear all,
Changing the function signature to IMMUTABLE worked like a dream. No issue now. Sorry for my confusion on VOLATILE being created as the default. Thanks to everyone for your help!
Kind regards,
Rick Vincent
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Friday, April 17, 2020 4:55 PM
To: Rick Vincent <rvincent@temenos.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@postgresql.org; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
Subject: Postgres not using index on views
On Friday, April 17, 2020, Rick Vincent <rvincent@temenos.com> wrote:
I was wondering if anyone can explain the below problem. Should a bug be logged for this?
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.
The information in this e-mail and any attachments is confidential and may be legally privileged. It is intended solely for the addressee or addressees. Any use or disclosure of the contents of this e-mail/attachments by a not intended recipient is unauthorized and may be unlawful. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of TEMENOS. We recommend that you check this e-mail and any attachments against viruses. TEMENOS accepts no liability for any damage caused by any malicious code or virus transmitted by this e-mail.