RE: Postgres not using index on views

2020-04-17 Thread Rick Vincent 
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' ; Justin Pryzby 
Cc: [email protected]; Manoj Kumar ; 
Herve Aubert 
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 relpagesreltuples   relallvisible   relkind relnatts
relhassubclass  reloptions  pg_table_size
FBNK_CURRENCY   6   93  0   r   2   false   NULL81920

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 mailto:[email protected]>>
Sent: Tuesday, April 7, 2020 7:09 AM
To: Justin Pryzby mailto:[email protected]>>
Cc: Rick Vincent mailto:[email protected]>>; 
[email protected]; 
Manoj Kumar mailto:[email protected]>>; Herve 
Aubert mailto:[email protected]>>
Subject: Re: Postgres not using index on views

Justin Pryzby mailto:[email protected]>> writes:
> On Mon, Apr 06, 2020 at 02:19:59PM +, 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

Postgres not using index on views

2020-04-17 Thread David G. Johnston 
On Friday, April 17, 2020, Rick Vincent  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' ; Justin Pryzby 
> *Cc:* [email protected]; Manoj Kumar <
> [email protected]>; Herve Aubert 
> *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.

RE: Postgres not using index on views

2020-04-17 Thread Rick Vincent 
Hi David,

Oh, okay…I missed that implied part.  Will try it and post back.

Thanks,
Rick

From: David G. Johnston 
Sent: Friday, April 17, 2020 4:55 PM
To: Rick Vincent 
Cc: Tom Lane ; Justin Pryzby ; 
[email protected]; Manoj Kumar ; Herve 
Aubert 
Subject: Postgres not using index on views

On Friday, April 17, 2020, Rick Vincent 
mailto:[email protected]>> 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' mailto:[email protected]>>; Justin Pryzby 
mailto:[email protected]>>
Cc: [email protected]; 
Manoj Kumar mailto:[email protected]>>; Herve 
Aubert mailto:[email protected]>>
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.