Re: [EXT] Re: Problem getting query to use index inside a function

"Dirschel, Steve" <steve(dot)dirschel(at)thomsonreuters(dot)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 function is (by default) VOLATILE. Try marking it STABLE so that it can share the 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 =
ANY($1)
AND productid=$2

$function$
stable ;

explain
SELECT objectid::text, n::text, v::text, vt::int FROM
steve1(ARRAY['5ab8e0ca-abb5-48c9-a95e-2bb2a375d903','adcbe251-6723-48a8-8385-55133fab704a'], 1);
--- CUT ---

and I got:

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.15..11.42 rows=100 width=100)
-> Index Scan using request_objectid_productid_idx on request r (cost=0.15..8.17 rows=1 width=64)
Index Cond: ((objectid = ANY ('{5ab8e0ca-abb5-48c9-a95e-2bb2a375d903,adcbe251-6723-48a8-8385-55133fab704a}'::text[])) AND (productid = 1))
-> Function Scan on jsonb_array_elements i (cost=0.01..1.00 rows=100 width=32)
(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