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

>> 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

Hi Tom,

I ran your code and got similar results so I agree there is more for me to dig into. I see STABLE has a limit of not allowing DML in the function. I am testing against a very simple function here but we have many other functions with the same problem but those also contain DML so even if I got the STABLE to work in this one test case it does not appear I could use in all of my functions with this problem. Are there other options here besides the STABLE option that would work for functions that also contain DML?

Thanks
Steve