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
