Subject: Re: Postgres not using index on views
To: pgsql-performance@lists.postgresql.org
References: 
 <DM6PR04MB492354C0895FB26EDFBE1D82C1C20@DM6PR04MB4923.namprd04.prod.outlook.com>
 <20200407045929.GJ2228@telsasoft.com> <26256.1586236149@sss.pgh.pa.us>
 <DM6PR04MB49231EBFFF7CFB2F18634326C1C30@DM6PR04MB4923.namprd04.prod.outlook.com>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <e24f881c-2475-2ad9-ca12-fa594a057196@gmx.net>
Date: Tue, 7 Apr 2020 11:18:04 +0200
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
In-Reply-To: 
 <DM6PR04MB49231EBFFF7CFB2F18634326C1C30@DM6PR04MB4923.namprd04.prod.outlook.com>
Content-Type: text/plain; charset=utf-8
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

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.
=C2=A0
> CREATE OR REPLACE FUNCTION extractValueJS (sVar text, nfm INTEGER, nvm I=
NTEGER)
> RETURNS VARCHAR as $$
> declare
> sRet text :=3D '';
> nSize int :=3D 0;
> retVal int :=3D 0;
> cVar text[] :=3D regexp_split_to_array(sVar,'');
> idx int :=3D 1;
> nStart int :=3D 0;
> nEnd int :=3D 0;
> begin
> etc...
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 return sRet;
> end;
> $$ LANGUAGE plpgsql;

You haven't shown us your actual code, but if you can turn that into a "la=
nguage sql" function (defined as immutable, or at least stable), I would e=
xpect it to be way more efficient.

Thomas