"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 function is (by default) VOLATILE. Try marking it
STABLEso 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
