RPC Function not using index

[shubham941122]

Hey Guys,

Environment

• PostgreSQL version: 14
• PostgREST version: 11

Below are details of schema, function & underlying index.

TABLE:

CREATE TABLE IF NOT EXISTS registry.req_resp
(
    activity_id uuid NOT NULL,
    service_id character varying(120) COLLATE pg_catalog."default",
    request_payload jsonb,
    response_payload jsonb,
    created_date timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modified_date timestamp without time zone,
    activity_log jsonb,
    client_request_id character varying(120) COLLATE pg_catalog."default",
    app_name character varying(120) COLLATE pg_catalog."default"
)

FUNCTION:

CREATE OR REPLACE FUNCTION registry.fn_req_resp_filters_btree_index(
    p_activity_id UUID DEFAULT NULL,
    p_service_id VARCHAR DEFAULT NULL,
    p_order_id TEXT DEFAULT NULL,
    p_client_request_id VARCHAR DEFAULT NULL
)
RETURNS TABLE (
    r_activity_id UUID,
    r_service_id VARCHAR,
    r_created_date TIMESTAMP,
    r_activity_log JSONB,
    r_client_request_id VARCHAR,
    r_app_name VARCHAR
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        activity_id,
        service_id,
        created_date,
        activity_log,
	client_request_id,
	app_name
    FROM 
        registry.req_resp
    WHERE 
        (p_activity_id IS NULL OR activity_id = p_activity_id) AND
        (p_service_id IS NULL OR service_id = p_service_id) AND
	(p_client_request_id IS NULL OR client_request_id = p_client_request_id) AND
        (p_order_id IS NULL OR (activity_log->'requestBody'->'orderInfo'->>'orderId') = p_order_id)
    ORDER BY 
        created_date DESC;
END;
$$ LANGUAGE plpgsql;

INDEX:

CREATE INDEX IF NOT EXISTS idx_activity_log_order_id_btree
    ON registry.req_resp USING btree
    ((((activity_log -> 'requestBody'::text) -> 'orderInfo'::text) ->> 'orderId'::text) COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

When i pd RPC call, getting below query plan for the same

/rpc/fn_req_resp_filters_btree_index?p_order_id=661748370&offset=0&limit=100

Aggregate  (cost=23.78..23.80 rows=1 width=112)
  CTE pgrst_source
    ->  Nested Loop  (cost=0.26..20.27 rows=1000 width=152)
          ->  Limit  (cost=0.01..0.01 rows=1 width=32)
                ->  Function Scan on json_to_recordset _  (cost=0.01..1.00 rows=100 width=32)
          ->  Function Scan on fn_req_resp_filters_btree_index pgrst_call  (cost=0.25..10.25 rows=1000 width=152)
  ->  Subquery Scan on _postgrest_t  (cost=0.00..3.00 rows=100 width=176)
        ->  Limit  (cost=0.00..2.00 rows=100 width=152)
              ->  CTE Scan on pgrst_source record  (cost=0.00..20.00 rows=1000 width=152)

Problem

Not able to get any indication of index usage for this query, Any ideas/suggestions?

[wolfgangwalther]

Not able to get any indication of index usage for this query

You are not demonstrating that.

The RPC you have is not marked as STABLE and is a plpgsql function. Both prevent function inlining. You can tell from your explain, because there is a "Function Scan" node in it.

This means that inside this "Function Scan" node the function is executed - and the query inside the function is planned and executed separately. You have no information whatsoever in your explain about how that inner query looks like.

Since this is a simple function, which can be expressed as SQL, you might want to rewrite it as such and mark it as STABLE (it is, since it's a plain select only). Then you can try again and you should get one big explain, which includes the inner query as part of the outer one.

Alternatively, just run the query inside the function yourself directly and replace the arguments with values. Then check whether the index is used or not.

[wolfgangwalther]

After changing to sql function, below is the plan given by server directly, which i assume is good for my use case.
And even though postgrest is displaying something diff, it will be doing the same.

No, that's not correct. After you changed to SQL, you are not having a function scan anymore, the query is planned in one - and in this case the PostgREST plan shows that you have a seq scan.

This only applies for the plpgsql function.

[shubham941122]

OK. Let me explore more on that.

Thanks for the timely help, @wolfgangwalther .

[shubham941122]

SQL Function plans:

Directly on postgreSQL server:

EXPLAIN ANALYZE SELECT * FROM fn_req_resp_filters(p_order_id:='551748370');

"Sort  (cost=572.68..573.10 rows=166 width=976) (actual time=24.645..24.893 rows=1589 loops=1)"
"  Sort Key: req_resp.created_date DESC"
"  Sort Method: quicksort  Memory: 2443kB"
"  ->  Bitmap Heap Scan on req_resp  (cost=5.29..566.56 rows=166 width=976) (actual time=0.388..22.440 rows=1589 loops=1)"
"        Recheck Cond: ((((activity_log -> 'requestBody'::text) -> 'orderInfo'::text) ->> 'orderId'::text) = '551748370'::text)"
"        Heap Blocks: exact=1144"
"        ->  Bitmap Index Scan on idx_activity_log_order_id  (cost=0.00..5.25 rows=166 width=0) (actual time=0.145..0.146 rows=1589 loops=1)"
"              Index Cond: ((((activity_log -> 'requestBody'::text) -> 'orderInfo'::text) ->> 'orderId'::text) = '551748370'::text)"
"Planning Time: 0.234 ms"
"Execution Time: 25.278 ms"

RPC call via postgrest:

/rpc/fn_req_resp_filters?p_order_id=551748370
Accept: application/vnd.pgrst.plan

Aggregate  (cost=5092.94..5092.96 rows=1 width=112)
  ->  Nested Loop  (cost=5083.82..5091.29 rows=331 width=976)
        ->  Limit  (cost=0.01..0.01 rows=1 width=32)
              ->  Function Scan on json_to_recordset _  (cost=0.01..1.00 rows=100 width=32)
        ->  Sort  (cost=5083.81..5084.64 rows=331 width=976)
              Sort Key: req_resp.created_date DESC
              ->  Result  (cost=0.00..5069.96 rows=331 width=976)
                    One-Time Filter: (_.p_order_id IS NOT NULL)
                    ->  Seq Scan on req_resp  (cost=0.00..5069.96 rows=331 width=951)
                          Filter: ((_.p_order_id IS NULL) OR ((((activity_log -> 'requestBody'::text) -> 'orderInfo'::text) ->> 'orderId'::text) = _.p_order_id))

I am still confused on why different plans are returned & how will the execution from postgrest be diff even if i am calling the same function?
Is there some bug here?

P.S. Thanks for all the help.

[wolfgangwalther]

I am still confused on why different plans are returned & how will the execution from postgrest be diff even if i am calling the same function?

That's because PostgREST is calling the function as part of a much bigger query. This query's structure doesn't allow PostgreSQL to use the same query plan.

[wolfgangwalther]

Is there some bug here?

No.