How to get query runtime in PL/pgSQL to be as fast as console query runtime?
The elephant in the room is function overhead. When calling a function instead of raw SQL, Postgres needs to look up the function in the system catalogs (possibly picking the best match in case of function overloading) and consider settings of the function. And then there is the overhead for the function call itself. This adds a tiny overhead cost.

That only really matters for very simple queries like your example - which should much rather be implemented with a plain SQL function. You still encounter some (tiny) function overhead with an SQL function, but it can be "inlined" if it's a simple SELECT and some preconditions are met.

Like always, repeated calls profit from the populated cache. And in the case of plpgsql functions, it may also start to use a generic plan after some (typically 5) calls, if that seems promising.

Consider this related discussion on pgsql-general.

Query plans for code inside plpgsql functions

You are wondering:


  explain analyze even shows the usage of indexes in the first case but not on the second [plpgsql function].


plpgsql functions are black boxes to the query planner, which does not examine the contents of the function. They act as optimization barriers. Unlike with SQL functions their content cannot be inlined in outer queries.

This is the reason why EXPLAIN ANALYZE doesn't show what's happening inside the plpgsql function and you therefore see no index scan. The index (most probably) is still used. You can use the additional module auto_explain to have a peek on the query plans for SQL code inside the function. Consider the answers to this related question for details:


Postgres query plan of a UDF invocation written in pgpsql