Hi,
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)
Planning Time: 0.679 ms
Execution Time: 78642.616 ms
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)
Planning Time: 0.031 ms
Execution Time: 0.288 ms
So you can see that postgresql is not using the primary key index for RECID.
THIS IS NOT THE CASE FOR ORACLE where the primary key index is used in the
explain plan
The view is created similar to the following where extractValueJS is a stored
procedure that extracts a value from the VARCHAR XMLRECORD column.
CREATE VIEW "V_FBNK_CUSTOMER" as
SELECT a.RECID, a.XMLRECORD "THE_RECORD"
,a.RECID "CUSTOMER_CODE"
,a.RECID "CUSTOMER_NO"
,extractValueJS(a.XMLRECORD, 1, 0) "MNEMONIC"
,extractValueJS(a.XMLRECORD, 2, 0) "SHORT_NAME"
,extractValueJS(a.XMLRECORD, 2, -1) "SHORT_NAME_2"
, etc
, extractValueJS(a.XMLRECORD, 179, 9) "TESTER"
FROM
"FBNK_CUSTOMER" a
As well, the problem gets worse as columns are added to the view, irrespective
of the SELECTION columns and it seems to perform some activity behind.
Creating an empty view,
CREATE VIEW "V_FBNK_CUSTOMER_TEST" as
SELECT a.RECID, a.XMLRECORD "THE_RECORD"
,a.RECID "CUSTOMER_CODE"
,a.RECID "CUSTOMER_NO"
FROM
"FBNK_CUSTOMER" a ------------- > 3 ms select RECID from
"V_FBNK_CUSTOMER_TEST"
CREATE VIEW "V_FBNK_CUSTOMER_TEST" as
SELECT a.RECID, a.XMLRECORD "THE_RECORD"
,a.RECID "CUSTOMER_CODE"
,a.RECID "CUSTOMER_NO"
,extractValueJS(a.XMLRECORD, 1, 0) "MNEMONIC"
FROM
"FBNK_CUSTOMER" a ------------------> 54 ms select RECID from
"V_FBNK_CUSTOMER_TEST"
CREATE VIEW "V_FBNK_CUSTOMER_TEST" as
SELECT a.RECID, a.XMLRECORD "THE_RECORD"
,a.RECID "CUSTOMER_CODE"
,a.RECID "CUSTOMER_NO"
,extractValueJS(a.XMLRECORD, 1, 0) "MNEMONIC"
,extractValueJS(a.XMLRECORD, 2, 0) "SHORT_NAME"
FROM
"FBNK_CUSTOMER" a ------------------------> 118 ms select RECID
from "V_FBNK_CUSTOMER_TEST"
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?
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=102172.500..102172.501 rows=1 loops=1)
Sort Key: "V_FBNK_CUSTOMER".recid
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on "V_FBNK_CUSTOMER" (cost=0.00..19015.05 rows=1 width=7)
(actual time=91242.866..102172.474 rows=1 loops=1)
Filter: (("V_FBNK_CUSTOMER"."TESTER")::text = '5.00'::text)
Rows Removed by Filter: 179
-> Seq Scan on "FBNK_CUSTOMER" a (cost=0.00..19012.80 rows=180
width=14575) (actual time=613.455..102172.175 rows=180 loops=1)
Planning Time: 1.674 ms
Execution Time: 102174.015 ms
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.
