Postgres Function Slower than Query / Postgres 8.4

Question

I (must) use a Postgres 8.4 Database. In this Database i create a function:

CREATE OR REPLACE FUNCTION counter (mindate timestamptz,maxdate timestamptz) RETURNS integer AS $func$ 
DECLARE result integer; 
BEGIN
Select INTO result COUNT(*) AS counter from columname where starttime BETWEEN $1 and $2;
Return result;  
END
$func$ LANGUAGE plpgsql;

The Query:

apptelemetry=# select counter('2016-03-01 00:00:00','2016-03-11 00:00:00');
 requestcounter
----------------
           8893
(1 row)

Time: 4.740 ms

When i make a normal Query:

apptelemetry=# select Count(*) from columname where starttime BETWEEN '2016-03-01 00:00:00' AND '2016-03-11 00:00:00';
 count
-------
  8893
(1 row)

Time: 3.214 ms

Why is the function slower than the normal query? Have someone performance tips for me?

Regrads

Answer 1

The queries in PLpgSQL are blindly optimized. Sometimes a execution plan can be suboptimal for some parameters.

The PLpgSQL reuses execution plan of any query. It has some advantages (there are no repeated expensive query planning), but it has some disadvantages too (the plan is not optimized for real values, it is optimized for the most probably values). The modern versions of Postgres are little bit smarter.

The solution of this query is dynamic SQL. In this case the execution plan is not reused, and then can be optimized for real parameter's values:

CREATE OR REPLACE FUNCTION counter (mindate timestamptz,maxdate timestamptz) RETURNS integer AS $func$ 
DECLARE result integer; 
BEGIN
  EXECUTE 'SELECT COUNT(*) FROM columname 
             WHERE starttime BETWEEN $1 and $2'
     INTO result USING mindate, maxdate;
  RETURN result;  
END
$func$ LANGUAGE plpgsql;

One article about this issue: http://blog.endpoint.com/2008/12/why-is-my-function-slow.html