Problem getting query to use index inside a function

From: "Dirschel, Steve" <steve(dot)dirschel(at)thomsonreuters(dot)com>
To: "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Problem getting query to use index inside a function
Date: 2025-11-05 16:02:27
Message-ID: BL1PR03MB60386561F48885BEEA14C575FAC5A@BL1PR03MB6038.namprd03.prod.outlook.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2025-11-05 16:18:39 Re: Problem getting query to use index inside a function
Previous Message Andreas Joseph Krogh 2025-11-05 08:18:06 trgm and index-usage when using similarity function