How to get query runtime in PL/pgSQL to be as fast as console query runtime?

Question

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)
     ->  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.

Answer 1

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 a tiny overhead for the function call itself.

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 populated cache. And in the case of PL/pgSQL 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 PL/pgSQL functions

You wonder:

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

PL/pgSQL 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.

Accordingly, EXPLAIN ANALYZE doesn't show what's happening inside the PL/pgSQL function, so we see no index scan. The index is still used (most probably). You can use the additional module auto_explain to have a peek at query plans for SQL statements inside the function. See:

• Get query plan for SQL statement nested in a PL/pgSQL function