From: Rick Vincent <rvincent@temenos.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Manoj Kumar <manojkumar@temenos.com>
Cc: Herve Aubert <haubert@temenos.com>
Subject: RE: Postgres not using index on views
Date: Tue, 7 Apr 2020 09:08:06 +0000
Message-ID: <DM6PR04MB49231EBFFF7CFB2F18634326C1C30@DM6PR04MB4923.namprd04.prod.outlook.com> (raw)
In-Reply-To: <26256.1586236149@sss.pgh.pa.us>
References: <DM6PR04MB492354C0895FB26EDFBE1D82C1C20@DM6PR04MB4923.namprd04.prod.outlook.com>
<20200407045929.GJ2228@telsasoft.com>
<26256.1586236149@sss.pgh.pa.us>
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.
CREATE OR REPLACE FUNCTION extractValueJS (sVar text, nfm INTEGER, nvm INTEGER)
RETURNS VARCHAR as $$
declare
sRet text := '';
nSize int := 0;
retVal int := 0;
cVar text[] := regexp_split_to_array(sVar,'');
idx int := 1;
nStart int := 0;
nEnd int := 0;
begin
etc...
return sRet;
end;
$$ LANGUAGE plpgsql;
After reading you link.....
Here is a better explain plan:
Explain on the table:
EXPLAIN (analyze,BUFFERS)
select RECID from "FBNK_CUSTOMER"
Seq Scan on "FBNK_CUSTOMER" (cost=0.00..22.80 rows=180 width=7) (actual time=0.011..0.073 rows=180 loops=1)
Buffers: shared hit=21
Planning Time: 0.056 ms
Execution Time: 0.091 ms
Explain on the view:
EXPLAIN (analyze,BUFFERS)
select RECID from "V_FBNK_CUSTOMER"
Subquery Scan on "V_FBNK_CUSTOMER" (cost=0.00..19014.60 rows=180 width=7) (actual time=455.727..76837.097 rows=180 loops=1)
Buffers: shared hit=204
-> Seq Scan on "FBNK_CUSTOMER" a (cost=0.00..19012.80 rows=180 width=14575) (actual time=455.726..76836.791 rows=180 loops=1)
Buffers: shared hit=204
Planning Time: 1.109 ms
Execution Time: 76838.505 ms
Explain on view with a column:
EXPLAIN (analyze,BUFFERS)
SELECT RECID FROM "V_FBNK_CUSTOMER" WHERE "TESTER" = '5.00' ORDER BY RECID
Sort (cost=19015.06..19015.06 rows=1 width=7) (actual time=76033.475..76033.475 rows=1 loops=1)
Sort Key: "V_FBNK_CUSTOMER".recid
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=21
-> Subquery Scan on "V_FBNK_CUSTOMER" (cost=0.00..19015.05 rows=1 width=7) (actual time=66521.952..76033.434 rows=1 loops=1)
Filter: (("V_FBNK_CUSTOMER"."TESTER")::text = '5.00'::text)
Rows Removed by Filter: 179
Buffers: shared hit=21
-> Seq Scan on "FBNK_CUSTOMER" a (cost=0.00..19012.80 rows=180 width=14575) (actual time=462.949..76033.096 rows=180 loops=1)
Buffers: shared hit=21
Planning Time: 0.819 ms
Execution Time: 76033.731 ms
But on the underlying table and not the view but just using the one view column called TESTER:
EXPLAIN (analyze,BUFFERS)
SELECT RECID FROM "FBNK_CUSTOMER" WHERE extractValueJS(XMLRECORD, 179, 9) = '5.00' ORDER BY RECID
Sort (cost=68.26..68.27 rows=1 width=7) (actual time=220.403..220.404 rows=1 loops=1)
Sort Key: recid
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=21
-> Seq Scan on "FBNK_CUSTOMER" (cost=0.00..68.25 rows=1 width=7) (actual time=193.000..220.397 rows=1 loops=1)
Filter: ((extractvaluejs((xmlrecord)::text, 179, 9))::text = '5.00'::text)
Rows Removed by Filter: 179
Buffers: shared hit=21
Planning Time: 0.045 ms
Execution Time: 220.418 ms
Other info:
SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE relname='FBNK_CURRENCY';
relname relpages reltuples relallvisible relkind relnatts relhassubclass reloptions pg_table_size
FBNK_CURRENCY 6 93 0 r 2 false NULL 81920
Version is:
PostgreSQL 11.7 (Debian 11.7-2.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
It is a postgres docker image.
Thanks,
Rick
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, April 7, 2020 7:09 AM
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Rick Vincent <rvincent@temenos.com>; pgsql-performance@postgresql.org; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
Subject: Re: Postgres not using index on views
Justin Pryzby <pryzby@telsasoft.com<mailto:pryzby@telsasoft.com>> writes:
> On Mon, Apr 06, 2020 at 02:19:59PM +0000, Rick Vincent wrote:
>> 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 ?
The planner seems to be quite well aware that the slower query is going to be slower, since the estimated costs are much higher. Since it's not choosing to optimize into a faster form, I wonder whether it's constrained by semantic requirements. In particular, I'm suspicious that some of those functions you have in the view are marked "volatile", preventing them from being optimized away.
Beyond that guess, though, there's really not enough info here to say.
The info we usually ask for to debug slow-query problems is explained at
https://wiki.postgresql.org/wiki/Slow_Query_Questions
regards, tom lane
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.
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: rvincent@temenos.com, tgl@sss.pgh.pa.us, pryzby@telsasoft.com, manojkumar@temenos.com, haubert@temenos.com
Subject: RE: Postgres not using index on views
In-Reply-To: <DM6PR04MB49231EBFFF7CFB2F18634326C1C30@DM6PR04MB4923.namprd04.prod.outlook.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