From: Thomas Kellerer <shammat@gmx.net>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Postgres not using index on views
Date: Tue, 7 Apr 2020 11:18:04 +0200
Message-ID: <e24f881c-2475-2ad9-ca12-fa594a057196@gmx.net> (raw)
In-Reply-To: <DM6PR04MB49231EBFFF7CFB2F18634326C1C30@DM6PR04MB4923.namprd04.prod.outlook.com>
References: <DM6PR04MB492354C0895FB26EDFBE1D82C1C20@DM6PR04MB4923.namprd04.prod.outlook.com>
<20200407045929.GJ2228@telsasoft.com>
<26256.1586236149@sss.pgh.pa.us>
<DM6PR04MB49231EBFFF7CFB2F18634326C1C30@DM6PR04MB4923.namprd04.prod.outlook.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: shammat@gmx.net, pgsql-performance@lists.postgresql.org
Subject: Re: Postgres not using index on views
In-Reply-To: <e24f881c-2475-2ad9-ca12-fa594a057196@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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