3

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.

  • Show the explain analyze result(s), please. – Craig Ringer Feb 6 '14 at 12:44
  • Also, what's your operating system? Does anything (at all) else run on the same machine? Those kinds of times are not far from normal scheduler jitter and measurement error, you really need to aggregate them into larger loop runs that take multiple seconds to usefully compare them. – Craig Ringer Feb 6 '14 at 12:54
  • I'm running Mac OSX and the production server runs ArchLinux. However, I dont't see why that matters, since running the same query in the same machine but in two different contexts yields two pretty different runtimes. – Oxfist Feb 6 '14 at 13:12
  • 1
    Why are you using language "plpgsql" when you could be using language "sql"? This should have lower execution overhead. – Colin 't Hart Feb 6 '14 at 13:15
  • You're right, that was one of the things I tried, like was said in the SO link I gave. However this had no effect on the runtime. – Oxfist Feb 6 '14 at 13:16
5

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:

  • What exactly do you mean by inlined? – Oxfist Feb 6 '14 at 14:44
  • 1
    300+ms for the first call and 100ms per function execution with a warm cache seems too expensive an overhead to me. I wonder if there's some room for optimisation here? – Colin 't Hart Feb 6 '14 at 14:54
  • 1
    @Oxfist: Funcion inlining means that (simple) sql queries can be taken from an sql function to replace the function call in the context of a bigger query, thereby eliminating the function overhead for repeated calls. – Erwin Brandstetter Feb 6 '14 at 14:57
  • That's why I suggested language "SQL" rather than "plpgsql" as comment on the question. – Colin 't Hart Feb 6 '14 at 14:59
  • @Colin'tHart: +1 to that. I may be mistaken but it seems to me that indexes aren't being used at all inside the function. – Oxfist Feb 6 '14 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.