public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dirschel, Steve <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: [EXT] Re: Problem getting query to use index inside a function
Date: Wed, 5 Nov 2025 20:18:18 +0000
Message-ID: <BL1PR03MB6038D428E038A78B7807F749FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
References: <BL1PR03MB60386561F48885BEEA14C575FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
<[email protected]>
<BL1PR03MB60380B355BF275F767A24245FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
<[email protected]>
>> 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
view thread (5+ messages)
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: <BL1PR03MB6038D428E038A78B7807F749FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
* 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