public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Dirschel, Steve <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: [EXT] Re: Problem getting query to use index inside a function
Date: Wed, 05 Nov 2025 14:17:40 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <BL1PR03MB60380B355BF275F767A24245FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
References: <BL1PR03MB60386561F48885BEEA14C575FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
<[email protected]>
<BL1PR03MB60380B355BF275F767A24245FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
"Dirschel, Steve" <[email protected]> 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
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: [EXT] Re: Problem getting query to use index inside a function
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox