From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Dirschel, Steve" <steve.dirschel@thomsonreuters.com>
cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: [EXT] Re: Problem getting query to use index inside a function
In-reply-to: 
 <BL1PR03MB60380B355BF275F767A24245FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
References: 
 <BL1PR03MB60386561F48885BEEA14C575FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
 <836752.1762359519@sss.pgh.pa.us>
 <BL1PR03MB60380B355BF275F767A24245FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
Comments: In-reply-to "Dirschel, Steve" <steve.dirschel@thomsonreuters.com>
	message dated "Wed, 05 Nov 2025 16:55:38 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <873495.1762370260.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 05 Nov 2025 14:17:40 -0500
Message-ID: <873496.1762370260@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/873496.1762370260%40sss.pgh.pa.us>
Precedence: bulk

"Dirschel, Steve" <steve.dirschel@thomsonreuters.com> writes:
>> I think you would have better luck if the planner were "inlining"
>> this function, which we can see it's not since you get a Function Scan =
on steve1 rather than the contained query.
>> I think the only thing stopping that from happening is that the functio=
n is (by default) VOLATILE.  Try marking it STABLE so that it can share th=
e calling query's snapshot.

> Thanks for the reply, but that did not seem to help.

I tried to replicate this as follows:

--- CUT ---
create table request(objectid text, productid int, data jsonb);
create index on request(objectid, productid);

CREATE OR REPLACE FUNCTION public.steve1(param_requestid text[], =

param_productid integer DEFAULT 1) RETURNS TABLE(objectid text, n =

text, v text, vt integer) LANGUAGE sql AS $function$
SELECT       objectid::text
             , i->>'n'::text
             , i->>'v'::text
             , (i->>'vt') :: INT as vt
FROM   request r
             , jsonb_array_elements(data -> 'i') i WHERE objectid =3D =

ANY($1)
             AND productid=3D$2

$function$
stable ;

explain
SELECT objectid::text, n::text, v::text, vt::int FROM =

steve1(ARRAY['5ab8e0ca-abb5-48c9-a95e-2bb2a375d903','adcbe251-6723-48a8-83=
85-55133fab704a'], 1);
--- CUT ---

and I got:

                                                                     QUERY=
 PLAN                                                                     =

--------------------------------------------------------------------------=
--------------------------------------------------------------------------
 Nested Loop  (cost=3D0.15..11.42 rows=3D100 width=3D100)
   ->  Index Scan using request_objectid_productid_idx on request r  (cost=
=3D0.15..8.17 rows=3D1 width=3D64)
         Index Cond: ((objectid =3D ANY ('{5ab8e0ca-abb5-48c9-a95e-2bb2a37=
5d903,adcbe251-6723-48a8-8385-55133fab704a}'::text[])) AND (productid =3D =
1))
   ->  Function Scan on jsonb_array_elements i  (cost=3D0.01..1.00 rows=3D=
100 width=3D32)
(4 rows)

which is what I expected from successful inlining of the function.
So there are some moving parts in your situation that you've not
told us about.

			regards, tom lane