How to benchmark two functions?

Question

I have read the following:

a plain VALUES expression in a plain SQL function is shorter and faster

I created two functions doing the same: one using PL/pgSQL and the other using SQL:

DROP DOMAIN IF EXISTS id CASCADE;
CREATE DOMAIN id AS int8
CHECK (VALUE >= 0);
DROP TYPE IF EXISTS modification CASCADE;
CREATE TYPE modification AS (
  moment timestamp,
  actor  id
);
SET current.actor = 'root';
DROP FUNCTION IF EXISTS new_modification_plpgsql();
CREATE FUNCTION new_modification_plpgsql() RETURNS modification
LANGUAGE plpgsql AS $$
DECLARE
BEGIN
  return row(now()::timestamp, current_setting('current.actor'));
END;
$$;
DROP FUNCTION IF EXISTS new_modification_sql();
CREATE FUNCTION new_modification_sql() RETURNS modification
LANGUAGE SQL AS $$
VALUES (now()::timestamp, current_setting('current.actor'));
$$;

How can I benchmark the two functions in order to find out which one is faster?

Answer 1

Here are four basic ways to measure execution time:

• Get execution time of PostgreSQL query

Be aware that performance testing can be tricky. Know what you are measuring exactly. Network latency? Time consumed by the client? Query planning vs. execution? Observer effect? And there are various noise factors, especially caching and competing work load on the server.

Plus, there is a whole lot of other factors with testing functions. Function labels can have a major impact. Sometimes, a function can be inlined, or query plans can be cached, or the function is not executed at all, or an immutable result is reused. Cheap functions like your examples must execute many thousand times to even show a measurable difference. (Use a LOOP in PL/pgSQL, or base on a bigger table.)

For your given, very basic examples, compare to these improved versions:

LANGUAGE plpgsql

CREATE OR REPLACE FUNCTION new_modification_plpgsql()
  RETURNS modification
  LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE AS
$func$
BEGIN
   RETURN (LOCALTIMESTAMP, current_setting('current.actor'))::modification;
END
$func$;

Most importantly, your original would not work at all. You defined actor as id, which is int8. current_setting() returns text. So you must cast explicitly. And you cannot SET current.actor = 'root';. Must be a valid bigint literal.

Also, add proper labels:

• IMMUTABLE (because it is), help repeated execution, allows using in index, ...

• PARALLEL SAFE (because it is): else it would stand in the way of parallelization.

LANGUAGE sql

CREATE FUNCTION new_modification_sql()
  RETURNS modification
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
   SELECT (LOCALTIMESTAMP, current_setting('current.actor'))::modification;
$func$;

Or, a shorter standard-SQL variant (same performance, mostly):

CREATE FUNCTION new_modification_sql()
  RETURNS modification
  LANGUAGE sql IMMUTABLE PARALLEL SAFE
RETURN (LOCALTIMESTAMP, current_setting('current.actor'))::modification;

See:

• What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?