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

> > Here is the function I'm having difficulties with:

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

> > Query:

> > shared=> explain (analyze, buffers)
> > shared-> SELECT objectid::text, n::text, v::text, vt::int FROM
> > shared-> steve1(ARRAY['5ab8e0ca-abb5-48c9-a95e-2bb2a375d903','adcbe251
> > shared-> -6723-48a8-8385-55133fab704a'], 1);
> > QUERY PLAN
> > ----------------------------------------------------------------------
> > ------------------------------------------
> > Function Scan on steve1 (cost=0.25..10.25 rows=1000 width=100)
> > (actual time=42.694..42.942 rows=3368 loops=1)

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

> (v18 should handle such cases better than previous versions, BTW.
> But you'd still be better off marking the function STABLE.)

> regards, tom lane

Thanks for the reply, but that did not seem to help. I tried using both the {} and ARRAY[] syntax but both are still full scanning based on the shared buffers. Any other ideas?

shared=> ALTER FUNCTION public.steve1 (param_requestid text[], param_productid integer) STABLE;
ALTER FUNCTION

shared=> explain (analyze, buffers)
shared-> SELECT objectid::text, n::text, v::text, vt::int FROM steve1('{83e3326a-62fe-45bc-81e5-1e9fb9a84d31}', 'PJJ', 1, 1);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Function Scan on steve1 (cost=0.25..10.25 rows=1000 width=100) (actual time=64.465..64.465 rows=0 loops=1)
Buffers: shared hit=16572
Planning Time: 0.033 ms
Execution Time: 64.485 ms
(4 rows)

shared=>
shared=> explain (analyze, buffers)
shared-> SELECT objectid::text, n::text, v::text, vt::int FROM steve1(ARRAY['83e3326a-62fe-45bc-81e5-1e9fb9a84d31'], 'PJJ', 1, 1);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Function Scan on steve1 (cost=0.25..10.25 rows=1000 width=100) (actual time=63.749..63.749 rows=0 loops=1)
Buffers: shared hit=16569
Planning Time: 0.043 ms
Execution Time: 63.766 ms
(4 rows)