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

Hi Justin,

You said, " Is there a reason why you don't store the extracted value in its own column ?"

RV>> It simply is the way the application stores the data.  For Oracle we are storing in XML and JSON format, for
postgres,due do limitations of XML api, we are storing in VARCHAR.  We can't break it out into columns very easily
becauseof the legacy application. 

You said, "It still did a seq scan on the table, so I'm not sure what this has to do with index scans ?"

RV>> On Oracle it will use the primary key index because it detects that all of the columns in the select clause are
indexable. With Postgres, it might be doing a seq scan but on a 180 rows, a select on the underlying table is many
timesfaster than the same select on the view.  It seems all of the view columns are being triggered which makes it
incrediblyslow. 

Thanks,
Rick



-----Original Message-----
From: Justin Pryzby <pryzby@telsasoft.com>
Sent: Tuesday, April 7, 2020 6:59 AM
To: Rick Vincent <rvincent@temenos.com>
Cc: pgsql-performance@postgresql.org; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
Subject: Re: Postgres not using index on views

On Mon, Apr 06, 2020 at 02:19:59PM +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
hasany suggestion. 
>
> A table is created as:
>
> CREATE TABLE "FBNK_CUSTOMER" (RECID VARCHAR(255) NOT NULL PRIMARY KEY,
> XMLRECORD VARCHAR)
>
> And contains only 180 rows.
>
> Doing an explain plan on the view created over this gives:
>
> EXPLAIN ANALYZE
> select RECID from "V_FBNK_CUSTOMER"
>
>
> Subquery Scan on "V_FBNK_CUSTOMER"  (cost=0.00..19014.60 rows=180 width=7) (actual time=459.601..78642.189 rows=180
loops=1)
>   ->  Seq Scan on "FBNK_CUSTOMER" a  (cost=0.00..19012.80 rows=180
> width=14575) (actual time=459.600..78641.950 rows=180 loops=1)
>
> Yet an Explain plan on the underlying table( on select RECID from "FBNK_CUSTOMER") gives:
>
> Seq Scan on "FBNK_CUSTOMER"  (cost=0.00..22.80 rows=180 width=7)
> (actual time=0.004..0.272 rows=180 loops=1)

It still did a seq scan on the table, so I'm not sure what this has to do with index scans ?

> The following query takes an extremely long time for only 180 rows, and what this means is that we would have to
indexanything appearing in the where clause for every table in order to use views because the views seem not to
considerthe select clause.  Why is that and does anyone know a way around this? 

Is there a reason why you don't store the extracted value in its own column ?
And maybe keep it up to date using an insert/update trigger on the xmlrecord column.

--
Justin

The information in this e-mail and any attachments is confidential and may be legally privileged. It is intended solely
forthe addressee or addressees. Any use or disclosure of the contents of this e-mail/attachments by a not intended
recipientis unauthorized and may be unlawful. If you have received this e-mail in error please notify the sender.
Pleasenote that any views or opinions presented in this e-mail are solely those of the author and do not necessarily
representthose of TEMENOS. We recommend that you check this e-mail and any attachments against viruses. TEMENOS accepts
noliability for any damage caused by any malicious code or virus transmitted by this e-mail.