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 16:55:38 +0000
Message-ID: <BL1PR03MB60380B355BF275F767A24245FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
References: <BL1PR03MB60386561F48885BEEA14C575FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com>
<[email protected]>
> > 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)
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: <BL1PR03MB60380B355BF275F767A24245FAC5A@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