Re: Postgres not using index on views

[Thomas Kellerer <shammat@xxxxxxx>]

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