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

this happen if you run the same query several times? and do you have index for starttime because this shouldnt be so slow no matter the order. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Mar 10, 2016 at 11:02
You don't really need PL/pgSQL for this. The 1ms overhead could well be caused by the overhead of calling PL/pgSQL. What happens if you change that to a simple language sql function? — user330315
– user330315, Commented Mar 10, 2016 at 11:07
"Why is the function slower than the normal query?" --- why should a function be faster? Just because you have wrapped something into a function does not make it faster automagically. — zerkms
– zerkms, Commented Mar 10, 2016 at 11:16

Pavel Stehule · Accepted Answer · 2016-03-10 20:07:18Z

8

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

answered Mar 10, 2016 at 20:07

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

liquid Over a year ago

Thank you very much! :-) Works a much better than mine!

Elger Mensonides Over a year ago

Downvoted, since custom plans in prepared statements (functions) is fixed since postgresql 9.2. Please edit your answer.

Ondřej Bouda Over a year ago

Upvoted, since changing an SQL function to a plpgsql implementation using RETURN QUERY EXECUTE '...' USING helped to speed up my query 10 times (with PostgreSQL 11.1). So this answer really helped me.

Pavel Stehule Over a year ago

@ElgerMensonides - Unfortunately, it was not fixed (I think so it is not principally possible). There is just new heuristic, that try to be little bit smarter (first 5 iterations uses custom plan, others depends on cost decisions), but there are reports so this design doesn't work 100% well. This heuristic is too simple, and cannot to work well for all cases.

Patrick · Accepted Answer · 2016-03-10 11:23:35Z

4

A PL/pgSQL function has overhead for setting up its environment. In your case, however, you could use a SQL language function which is much more efficient:

CREATE FUNCTION counter (mindate timestamptz, maxdate timestamptz) RETURNS integer AS
$func$ 
  SELECT count(*) FROM columname WHERE starttime BETWEEN $1 AND $2;
$func$ LANGUAGE sql;

Only when you need functionality that is not supported by the SQL language, like conditional branching, should you use PL/pgSQL.

That said, actual run times can be influenced by many things inside or outside of the DBMS so a simple comparison of a single run may be impacted by such influences.

answered Mar 10, 2016 at 11:23

Patrick

33k7 gold badges73 silver badges102 bronze badges

1 Comment

Pavel Stehule Over a year ago

In this case plpgsql has not to have any overhead,

Collectives™ on Stack Overflow

Postgres Function Slower than Query / Postgres 8.4

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related