public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dirschel, Steve <[email protected]>
To: [email protected] <[email protected]>
Subject: Problem getting query to use index inside a function
Date: Wed, 5 Nov 2025 16:02:27 +0000
Message-ID: <BL1PR03MB60386561F48885BEEA14C575FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com> (raw)
I'm not sure if I should send this to pgsql-performance or pqsql-general so hopefully I'm sending to the correct one.
Table definition:
shared=> \d request
Table "public.request"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+----------------
objectid | character(36) | | not null |
data | jsonb | | not null | '{}'::jsonb
clientid | character(3) | | not null |
active | integer | | not null |
productid | integer | | not null |
checkoutbyuid | character(100) | | |
checkoutdatetime | timestamp without time zone | | |
metadata | jsonb | | not null | '{}'::jsonb
search_vector | tsvector | | |
requeststate | text | | not null | 'Active'::text
Indexes:
"requestkey" PRIMARY KEY, btree (objectid, productid)
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 steve1(ARRAY['5ab8e0ca-abb5-48c9-a95e-2bb2a375d903','adcbe251-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)
Buffers: shared hit=16612 <-------------------
Planning Time: 0.034 ms
Execution Time: 43.279 ms
(4 rows)
The query is doing 16612 logical reads which implies it full scanning the table. I can also run the query providing the input values where you can clearly see it's full scanning the table:
shared=> explain (analyze, buffers)
shared-> SELECT objectid::text
shared-> , i->>'n'::text
shared-> , i->>'v'::text
shared-> , (i->>'vt') :: INT as vt
shared-> FROM request r
shared-> , jsonb_array_elements(data -> 'i') i
shared-> WHERE objectid = ANY( ARRAY['5ab8e0ca-abb5-48c9-a95e-2bb2a375d903','adcbe251-6723-48a8-8385-55133fab704a'])
shared-> AND productid=1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.01..19561.37 rows=57000 width=100) (actual time=0.351..41.354 rows=3368 loops=1)
Buffers: shared hit=16586 <-------------
-> Seq Scan on request r (cost=0.00..17566.36 rows=570 width=67) (actual time=0.010..38.341 rows=2 loops=1)
Filter: ((productid = 1) AND ((objectid)::text = ANY ('{5ab8e0ca-abb5-48c9-a95e-2bb2a375d903,adcbe251-6723-48a8-8385-55133fab704a}'::text[])))
Rows Removed by Filter: 57077
Buffers: shared hit=16569
-> Function Scan on jsonb_array_elements i (cost=0.01..1.00 rows=100 width=32) (actual time=0.291..0.406 rows=1684 loops=2)
Buffers: shared hit=17
Planning Time: 0.093 ms
Execution Time: 41.607 ms
(10 rows)
I can get the query to use the PK index if I change the query from ARRAY [] to {}:
shared=> explain (analyze, buffers)
shared-> SELECT objectid::text
shared-> , i->>'n'::text
shared-> , i->>'v'::text
shared-> , (i->>'vt') :: INT as vt
shared-> FROM request r
shared-> , jsonb_array_elements(data -> 'i') i
shared-> WHERE objectid = ANY('{5ab8e0ca-abb5-48c9-a95e-2bb2a375d903,adcbe251-6723-48a8-8385-55133fab704a}')
shared-> AND productid=1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.42..23.87 rows=200 width=100) (actual time=0.346..3.007 rows=3368 loops=1)
Buffers: shared hit=27 <----------------
-> Index Scan using requestkey on request r (cost=0.41..16.87 rows=2 width=67) (actual time=0.036..0.049 rows=2 loops=1)
Index Cond: ((objectid = ANY ('{5ab8e0ca-abb5-48c9-a95e-2bb2a375d903,adcbe251-6723-48a8-8385-55133fab704a}'::bpchar[])) AND (productid = 1))
Buffers: shared hit=10
-> Function Scan on jsonb_array_elements i (cost=0.01..1.00 rows=100 width=32) (actual time=0.274..0.388 rows=1684 loops=2)
Buffers: shared hit=17
Planning:
Buffers: shared hit=27
Planning Time: 0.176 ms
Execution Time: 3.286 ms
(11 rows)
But if I change the call to the function to use similar syntax the shared buffer hits of 16,586 is telling me it's still full scanning the table:
shared=> explain (analyze, buffers)
shared-> SELECT objectid::text, n::text, v::text, vt::int FROM steve1('{5ab8e0ca-abb5-48c9-a95e-2bb2a375d903,adcbe251-6723-48a8-8385-55133fab704a}', 1);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Function Scan on steve1 (cost=0.25..10.25 rows=1000 width=100) (actual time=56.708..56.970 rows=3368 loops=1)
Buffers: shared hit=16586 <---------------
Planning Time: 0.024 ms
Execution Time: 57.316 ms
(4 rows)
What do I need to change to get the query to use the PK index when executed inside the function?
Thanks in advance
Steve
This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
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]
Subject: Re: Problem getting query to use index inside a function
In-Reply-To: <BL1PR03MB60386561F48885BEEA14C575FAC5A@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