CODEWITHSUNDEEP
postgresqlplpgsqlsql-execution-plan
Aydin K.
Aydin K.

Reputation: 3368

PostgreSQL function having parameters is not using existing index

I have a PostgreSQL plpgsql function and noticed that it does not use an existing index on the MATERIALIZED VIEW (my_view), it is querying.

The special thing about this function is, that it invokes another function (check_user) passing its parameter v_username. The result is a boolean and dependant on its value the function decides with the case-construct, if the user gets all data from given destination view (my_view) back or a one, which is being joined with another table.

CREATE OR REPLACE function my_view_secured (v_username text)
  RETURNS setof my_view
  LANGUAGE plpgsql stable  
AS 
$function$
declare
  v_show_all boolean := check_user(v_username);
begin
    
CASE 
    WHEN v_show_all then return query select * from my_view;
    WHEN v_show_all = false then return query select st.* from my_view st join other_table st2 on st2.id = st.id;
end case;

end;
$function$
;

When executing the both queries defined in the CASE-WHEN part directly / without the function, PostgreSQL is using an existing index and the query is returning data quite fast (50ms). When invoking this wrapper function (my_view_secured), I assume the index is not used because it takes about 10-20 seconds to return.

select * from my_view --takes some ms

vs

select * from my_view_secured('RETURNS_TRUE') -- takes 10-20 secs, although same underlying query as the one above

(Maybe it is my DBeaver settings which falsely gives me the impression, that query 1 takes just some ms)

I have read here PostgreSQL is not using index when passed by param, that it is because due to the function parameter, PostgreSQL can not optimize the query. My first attempt was to rewrite this PLPGSQL style of function to a pure SQL-native query, but I struggle to find the correct logic for it.

From client perspective, I simply want to call the function with

SELECT .. FROM my_view_secured ('someusername')

and the function takes care about the data to be returned (either all or joined, depending on the return value from check_user function call). The index itself is set on an id field which is used by the join.

Does anyone has an idea, how to solve this issue?

Some additional informations:

PGSQL-Version: 13.6
my_view (MV): 19 million rows (index on id col)
other_table: 60k rows (unique index on id col)


LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1;
SET auto_explain.log_nested_statements = ON;
SET client_min_messages TO log;
  1. Query MV directly:
explain (analyze, verbose, buffers)  select * from my_view

Seq Scan on my_view  (cost=0.00..598633.52 rows=18902952 width=185) (actual time=0.807..15754.467 rows=18902952 loops=1)
  Output: <removed>
  Buffers: shared read=409604
Planning:
  Buffers: shared hit=67 read=8
Planning Time: 2.870 ms
Execution Time: 16662.400 ms

2a) Query MV via wrapper function (which returns all data / no join):

explain (analyze, verbose, buffers)  select * from my_view_secured ('some_username_returning_all_data') 

Function Scan on my_view_secured  (cost=0.25..10.25 rows=1000 width=3462) (actual time=9006.965..11887.518 rows=18902952 loops=1)
  Output: <removed>
  Function Call: my_view_secured('some_username_returning_to_all_data'::text)
  Buffers: shared hit=174 read=409590, temp read=353030 written=353030
Planning Time: 0.052 ms
Execution Time: 13091.509 ms

2b) Query MV via wrapper function (which returns joined data):

explain (analyze, verbose, buffers)  select * from my_view_secured ('some_username_triggering_join') 

Function Scan on my_view_secured  (cost=0.25..10.25 rows=1000 width=3462) (actual time=10183.590..11756.417 rows=8624367 loops=1)
  Output: <removed>
  Function Call: my_view_secured('some_username_triggering_join'::text)
  Buffers: shared hit=126 read=409792, temp read=161138 written=161138
Planning Time: 0.050 ms
Execution Time: 12434.169 ms

Upvotes: 2

Views: 1039

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658312

I just recreated your scenario and I get index scans for the nested queries as expected. Postgres absolutely can use indexes here.

PL/pgSQL handles nested SQL DML statements like this: every statement reached by control is parsed, planned and executed. Since neither of the two SELECT statements involves any parameters, those plans are saved immediately and reused on repeated execution. Either way, if a plain select * from my_view; "uses indexes", exactly the same should be the case for the nested statement.

There must be something going on that is not reflected in your question.

A couple of notes:

  • You misunderstood the linked answer. Your case is different as neither query involves parameters to begin with.

  • About ...

    does not use an existing index on the views (my_view), it is querying.

    Maybe just phrased ambiguously, but to be clear: there are no indexes on views. Tables (incl. MATERIALIZED VIEWs) can have indexes. A VIEW is basically just a stored query with some added secondary settings attached to an empty staging table with rewrite rules. Underlying tables may be indexed.

  • How do you know the nested queries do "not use an existing index" to begin with? It's not trivial to inspect query plans for nested statements like that. See:

It would seem you are barking up the wrong tree.

Upvotes: 1

Related Questions