RE: Postgres not using index on views - Mailing list pgsql-performance

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:

 

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

 

Explain on the view:

 

 

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

 

Explain on view with a column:

 

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

 

 

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

 

Other info:

 

 

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> 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

 

 

                        regards, tom lane