Message-ID: <1b2a5b7c6c3a5610be90944ab43c3b53a4def335.camel@cybertec.at>
Subject: Re: Postgres not using index on views
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Rick Vincent <rvincent@temenos.com>, "pgsql-performance@postgresql.org"
	 <pgsql-performance@postgresql.org>
Cc: Manoj Kumar <manojkumar@temenos.com>, Herve Aubert <haubert@temenos.com>
Date: Tue, 07 Apr 2020 08:42:35 +0200
In-Reply-To: 
 <DM6PR04MB492354C0895FB26EDFBE1D82C1C20@DM6PR04MB4923.namprd04.prod.outlook.com>
References: 
	<DM6PR04MB492354C0895FB26EDFBE1D82C1C20@DM6PR04MB4923.namprd04.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.32.5 (3.32.5-1.fc30) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Mon, 2020-04-06 at 14:19 +0000, Rick Vincent wrote:
> I am seeing a performance problem with postgresql v 11.7 on views, and I am wondering if
> anyone can tell me why or has any suggestion.

Your account is somewhat confused - too many questions rolled into one
rant, I would say.

There are two points that may clear up the case:

- If you have no WHERE clause, a sequential scan of the table is usually
  the best way to do it.  The exception is an index only scan if the index
  contains all that is required, but in PostgreSQL you need a recently
  VACUUMed table for that.

- The expensive part in your view is the "extractValueJS" function.
  Try to tune that for better performance.

If any of your problems are not explained by that, please say so.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com