RE: [EXT] Re: Problem getting query to use index inside a function - Mailing list pgsql-performance

> > 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
STABLEso 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
fullscanning 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)