How to get query runtime in PL/pgSQL to be as fast as console query runtime? Basically I want to assign the result of a query to a custom type attribute. However I noticed that querying directly from the PostgreSQL console was about 0.071 ms and inside the function was 0.400 ms and 0.170 ms after a few calls. explain analyze even shows the usage of indexes in the first case but not on the second.

Here's what I'm doing.

CREATE OR REPLACE FUNCTION fun_isliked(
    par_client client.id%type,
    par_feed feed.id%type
)
RETURNS boolean
AS
$$
BEGIN
    RETURN (
        EXISTS(
            SELECT
                1
            FROM
                feedlike fl
            WHERE
                fl.client = par_client
                AND fl.feed = par_feed
                AND fl.state = 1
        )
    );
END;
$$ LANGUAGE plpgsql STABLE;


Here are the outputs of explain analyze of the two cases described above:

postgres=# explain analyze select exists (select 1 from feedlike where client = 13 and feed = 68 and state = 1);
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=8.30..8.31 rows=1 width=0) (actual time=0.037..0.037 rows=1 loops=1)
   InitPlan 1 (returns $0)
     -&gt;  Index Scan using feedlike_client_feed_unique on feedlike  (cost=0.28..8.30 rows=1 width=0) (actual time=0.034..0.034 rows=1 loops=1)
           Index Cond: ((client = 13) AND (feed = 68))
           Filter: (state = 1)
 Total runtime: 0.086 ms

postgres=# explain analyze select * from fun_isliked(13, 68);
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Function Scan on fun_isliked  (cost=0.25..0.26 rows=1 width=1) (actual time=0.398..0.398 rows=1 loops=1)
 Total runtime: 0.416 ms


What is a possible workaround in order to effectively get same runtime inside the function? Is it even possible? Also, I'm running PostgreSQL 9.3.

I found that this question in SO had what I needed but after I tried everything in the selected answer and had no success on reducing the runtime I decided to ask a new question.
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