pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres not using index on views
12+ messages / 6 participants
[nested] [flat]
* Postgres not using index on views
@ 2020-04-06 14:19 Rick Vincent <rvincent@temenos.com>
2020-04-07 04:59 ` Re: Postgres not using index on views Justin Pryzby <pryzby@telsasoft.com>
2020-04-07 06:42 ` Re: Postgres not using index on views Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 12+ messages in thread
From: Rick Vincent @ 2020-04-06 14:19 UTC (permalink / raw)
To: pgsql-performance; +Cc: Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using index on views
2020-04-06 14:19 Postgres not using index on views Rick Vincent <rvincent@temenos.com>
@ 2020-04-07 04:59 ` Justin Pryzby <pryzby@telsasoft.com>
2020-04-07 05:09 ` Re: Postgres not using index on views Tom Lane <tgl@sss.pgh.pa.us>
2020-04-07 07:53 ` RE: Postgres not using index on views Rick Vincent <rvincent@temenos.com>
1 sibling, 2 replies; 12+ messages in thread
From: Justin Pryzby @ 2020-04-07 04:59 UTC (permalink / raw)
To: Rick Vincent <rvincent@temenos.com>; +Cc: pgsql-performance@postgresql.org, Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using index on views
2020-04-06 14:19 Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-07 04:59 ` Re: Postgres not using index on views Justin Pryzby <pryzby@telsasoft.com>
@ 2020-04-07 05:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-07 09:08 ` RE: Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-17 14:28 ` RE: Postgres not using index on views Rick Vincent <rvincent@temenos.com>
1 sibling, 2 replies; 12+ messages in thread
From: Tom Lane @ 2020-04-07 05:09 UTC (permalink / raw)
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>
Justin Pryzby <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
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: Postgres not using index on views
2020-04-06 14:19 Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-07 04:59 ` Re: Postgres not using index on views Justin Pryzby <pryzby@telsasoft.com>
2020-04-07 05:09 ` Re: Postgres not using index on views Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-04-07 09:08 ` Rick Vincent <rvincent@temenos.com>
2020-04-07 09:18 ` Re: Postgres not using index on views Thomas Kellerer <shammat@gmx.net>
1 sibling, 1 reply; 12+ messages in thread
From: Rick Vincent @ 2020-04-07 09:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using index on views
2020-04-06 14:19 Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-07 04:59 ` Re: Postgres not using index on views Justin Pryzby <pryzby@telsasoft.com>
2020-04-07 05:09 ` Re: Postgres not using index on views Tom Lane <tgl@sss.pgh.pa.us>
2020-04-07 09:08 ` RE: Postgres not using index on views Rick Vincent <rvincent@temenos.com>
@ 2020-04-07 09:18 ` Thomas Kellerer <shammat@gmx.net>
0 siblings, 0 replies; 12+ messages in thread
From: Thomas Kellerer @ 2020-04-07 09:18 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Rick Vincent schrieb am 07.04.2020 um 11:08:
> The function is defined as below, so no use of VOLATILE.
If you don't specify anything, the default is VOLATILE.
So your function *is* volatile.
> 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;
You haven't shown us your actual code, but if you can turn that into a "language sql" function (defined as immutable, or at least stable), I would expect it to be way more efficient.
Thomas
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: Postgres not using index on views
2020-04-06 14:19 Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-07 04:59 ` Re: Postgres not using index on views Justin Pryzby <pryzby@telsasoft.com>
2020-04-07 05:09 ` Re: Postgres not using index on views Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-04-17 14:28 ` Rick Vincent <rvincent@temenos.com>
2020-04-17 14:55 ` Postgres not using index on views David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Rick Vincent @ 2020-04-17 14:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
Hi,
I was wondering if anyone can explain the below problem. Should a bug be logged for this?
Kind regards,
Rick
_____________________________________________
From: Rick Vincent
Sent: Tuesday, April 7, 2020 11:08 AM
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-performance@postgresql.org; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
Subject: RE: Postgres not using index on views
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<mailto:tgl@sss.pgh.pa.us>>
Sent: Tuesday, April 7, 2020 7:09 AM
To: Justin Pryzby <pryzby@telsasoft.com<mailto:pryzby@telsasoft.com>>
Cc: Rick Vincent <rvincent@temenos.com<mailto:rvincent@temenos.com>>; pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>; Manoj Kumar <manojkumar@temenos.com<mailto:manojkumar@temenos.com>>; Herve Aubert <haubert@temenos.com<mailto: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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Postgres not using index on views
2020-04-06 14:19 Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-07 04:59 ` Re: Postgres not using index on views Justin Pryzby <pryzby@telsasoft.com>
2020-04-07 05:09 ` Re: Postgres not using index on views Tom Lane <tgl@sss.pgh.pa.us>
2020-04-17 14:28 ` RE: Postgres not using index on views Rick Vincent <rvincent@temenos.com>
@ 2020-04-17 14:55 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-17 15:12 ` RE: Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-20 11:10 ` RE: Postgres not using index on views Rick Vincent <rvincent@temenos.com>
0 siblings, 2 replies; 12+ messages in thread
From: David G. Johnston @ 2020-04-17 14:55 UTC (permalink / raw)
To: Rick Vincent <rvincent@temenos.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
On Friday, April 17, 2020, Rick Vincent <rvincent@temenos.com> wrote:
> Hi,
>
> I was wondering if anyone can explain the below problem. Should a bug be
> logged for this?
>
> Kind regards,
> Rick
>
> _____________________________________________
> *From:* Rick Vincent
> *Sent:* Tuesday, April 7, 2020 11:08 AM
> *To:* 'Tom Lane' <tgl@sss.pgh.pa.us>; Justin Pryzby <pryzby@telsasoft.com>
> *Cc:* pgsql-performance@postgresql.org; Manoj Kumar <
> manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
> *Subject:* RE: Postgres not using index on views
>
>
> 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.
>
>
IIUC as Tom wrote you have volatile functions (implied/default as Thomas
wrote) attached to view column outputs and the planner will not optimize
those away.
Mark your function immutable (assuming it is) and retry your experiment
with the where clause query.
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: Postgres not using index on views
2020-04-06 14:19 Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-07 04:59 ` Re: Postgres not using index on views Justin Pryzby <pryzby@telsasoft.com>
2020-04-07 05:09 ` Re: Postgres not using index on views Tom Lane <tgl@sss.pgh.pa.us>
2020-04-17 14:28 ` RE: Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-17 14:55 ` Postgres not using index on views David G. Johnston <david.g.johnston@gmail.com>
@ 2020-04-17 15:12 ` Rick Vincent <rvincent@temenos.com>
1 sibling, 0 replies; 12+ messages in thread
From: Rick Vincent @ 2020-04-17 15:12 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
Hi David,
Oh, okay…I missed that implied part. Will try it and post back.
Thanks,
Rick
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Friday, April 17, 2020 4:55 PM
To: Rick Vincent <rvincent@temenos.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@postgresql.org; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
Subject: Postgres not using index on views
On Friday, April 17, 2020, Rick Vincent <rvincent@temenos.com<mailto:rvincent@temenos.com>> wrote:
Hi,
I was wondering if anyone can explain the below problem. Should a bug be logged for this?
Kind regards,
Rick
_____________________________________________
From: Rick Vincent
Sent: Tuesday, April 7, 2020 11:08 AM
To: 'Tom Lane' <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>>; Justin Pryzby <pryzby@telsasoft.com<mailto:pryzby@telsasoft.com>>
Cc: pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>; Manoj Kumar <manojkumar@temenos.com<mailto:manojkumar@temenos.com>>; Herve Aubert <haubert@temenos.com<mailto:haubert@temenos.com>>
Subject: RE: Postgres not using index on views
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.
IIUC as Tom wrote you have volatile functions (implied/default as Thomas wrote) attached to view column outputs and the planner will not optimize those away.
Mark your function immutable (assuming it is) and retry your experiment with the where clause query.
David J.
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: Postgres not using index on views
2020-04-06 14:19 Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-07 04:59 ` Re: Postgres not using index on views Justin Pryzby <pryzby@telsasoft.com>
2020-04-07 05:09 ` Re: Postgres not using index on views Tom Lane <tgl@sss.pgh.pa.us>
2020-04-17 14:28 ` RE: Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-17 14:55 ` Postgres not using index on views David G. Johnston <david.g.johnston@gmail.com>
@ 2020-04-20 11:10 ` Rick Vincent <rvincent@temenos.com>
1 sibling, 0 replies; 12+ messages in thread
From: Rick Vincent @ 2020-04-20 11:10 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
Dear all,
Changing the function signature to IMMUTABLE worked like a dream. No issue now. Sorry for my confusion on VOLATILE being created as the default. Thanks to everyone for your help!
Kind regards,
Rick Vincent
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Friday, April 17, 2020 4:55 PM
To: Rick Vincent <rvincent@temenos.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@postgresql.org; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
Subject: Postgres not using index on views
On Friday, April 17, 2020, Rick Vincent <rvincent@temenos.com<mailto:rvincent@temenos.com>> wrote:
Hi,
I was wondering if anyone can explain the below problem. Should a bug be logged for this?
Kind regards,
Rick
_____________________________________________
From: Rick Vincent
Sent: Tuesday, April 7, 2020 11:08 AM
To: 'Tom Lane' <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>>; Justin Pryzby <pryzby@telsasoft.com<mailto:pryzby@telsasoft.com>>
Cc: pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org>; Manoj Kumar <manojkumar@temenos.com<mailto:manojkumar@temenos.com>>; Herve Aubert <haubert@temenos.com<mailto:haubert@temenos.com>>
Subject: RE: Postgres not using index on views
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.
IIUC as Tom wrote you have volatile functions (implied/default as Thomas wrote) attached to view column outputs and the planner will not optimize those away.
Mark your function immutable (assuming it is) and retry your experiment with the where clause query.
David J.
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* RE: Postgres not using index on views
2020-04-06 14:19 Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-07 04:59 ` Re: Postgres not using index on views Justin Pryzby <pryzby@telsasoft.com>
@ 2020-04-07 07:53 ` Rick Vincent <rvincent@temenos.com>
2020-04-07 09:24 ` Re: Postgres not using index on views Thomas Kellerer <shammat@gmx.net>
1 sibling, 1 reply; 12+ messages in thread
From: Rick Vincent @ 2020-04-07 07:53 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance; Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
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 because of 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 times faster than the same select on the view. It seems all of the view columns are being triggered which makes it incredibly slow.
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 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
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using index on views
2020-04-06 14:19 Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-07 04:59 ` Re: Postgres not using index on views Justin Pryzby <pryzby@telsasoft.com>
2020-04-07 07:53 ` RE: Postgres not using index on views Rick Vincent <rvincent@temenos.com>
@ 2020-04-07 09:24 ` Thomas Kellerer <shammat@gmx.net>
0 siblings, 0 replies; 12+ messages in thread
From: Thomas Kellerer @ 2020-04-07 09:24 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
> 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.
Why not use JSON in Postgres then?
Postgres' JSON functions are at least as powerful as Oracle's (if not better in a lot of areas).
Would be interesting to see what XML function/feature from Oracle you can't replicate/migrate to Postgres.
Another option might be to upgrade to Postgres 12 and define those columns as generated columns as part of the table, rather than a view.
Then you only pay the performance penalty of the extracValueJS() function when you update the table, not for every select.
Thomas
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres not using index on views
2020-04-06 14:19 Postgres not using index on views Rick Vincent <rvincent@temenos.com>
@ 2020-04-07 06:42 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 12+ messages in thread
From: Laurenz Albe @ 2020-04-07 06:42 UTC (permalink / raw)
To: Rick Vincent <rvincent@temenos.com>; pgsql-performance; +Cc: Manoj Kumar <manojkumar@temenos.com>; Herve Aubert <haubert@temenos.com>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2020-04-20 11:10 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-06 14:19 Postgres not using index on views Rick Vincent <rvincent@temenos.com>
2020-04-07 04:59 ` Justin Pryzby <pryzby@telsasoft.com>
2020-04-07 05:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-04-07 09:08 ` Rick Vincent <rvincent@temenos.com>
2020-04-07 09:18 ` Thomas Kellerer <shammat@gmx.net>
2020-04-17 14:28 ` Rick Vincent <rvincent@temenos.com>
2020-04-17 14:55 ` David G. Johnston <david.g.johnston@gmail.com>
2020-04-17 15:12 ` Rick Vincent <rvincent@temenos.com>
2020-04-20 11:10 ` Rick Vincent <rvincent@temenos.com>
2020-04-07 07:53 ` Rick Vincent <rvincent@temenos.com>
2020-04-07 09:24 ` Thomas Kellerer <shammat@gmx.net>
2020-04-07 06:42 ` Laurenz Albe <laurenz.albe@cybertec.at>
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