From: Justin Pryzby <pryzby@telsasoft.com>
To: Rick Vincent <rvincent@temenos.com>
Cc: pgsql-performance@postgresql.org, Manoj Kumar <manojkumar@temenos.com>
Cc: Herve Aubert <haubert@temenos.com>
Subject: Re: Postgres not using index on views
Date: Mon, 6 Apr 2020 23:59:29 -0500
Message-ID: <20200407045929.GJ2228@telsasoft.com> (raw)
In-Reply-To: <DM6PR04MB492354C0895FB26EDFBE1D82C1C20@DM6PR04MB4923.namprd04.prod.outlook.com>
References: <DM6PR04MB492354C0895FB26EDFBE1D82C1C20@DM6PR04MB4923.namprd04.prod.outlook.com>
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 has any 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 index anything appearing in the where clause for every table in order to use views because the views seem not to consider the 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, rvincent@temenos.com, manojkumar@temenos.com, haubert@temenos.com
Subject: Re: Postgres not using index on views
In-Reply-To: <20200407045929.GJ2228@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox