Problem getting query to use index inside a function - Mailing list 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