Function executes faster without STRICT modifier?

Question

I stumbled upon a slump in performance when a simple SQL function is declared STRICT while answering this question.

For demonstration, I created two variants of a function ordering two elements of an array in ascending order.

Test setup

Table with 10000 random pairs of integer (

CREATE TABLE tbl (arr int[]);

INSERT INTO tbl 
SELECT ARRAY[(random() * 1000)::int, (random() * 1000)::int]
FROM   generate_series(1,10000);

Function without STRICT modifier:

CREATE OR REPLACE FUNCTION f_sort_array(int[])
  RETURNS int[]
  LANGUAGE sql IMMUTABLE AS
$func$
SELECT CASE WHEN $1[1] > $1[2] THEN ARRAY[$1[2], $1[1]] ELSE $1 END;
$func$;

Function with STRICT modifier (otherwise identical):

CREATE OR REPLACE FUNCTION f_sort_array_strict(int[])
  RETURNS int[]
  LANGUAGE sql IMMUTABLE STRICT AS
$func$
SELECT CASE WHEN $1[1] > $1[2] THEN ARRAY[$1[2], $1[1]] ELSE $1 END;
$func$;

Results

I executed each around 20 times and took the best result from EXPLAIN ANALYZE.

SELECT f_sort_array(arr)        FROM tbl;  -- Total runtime:  43 ms
SELECT f_sort_array_strict(arr) FROM tbl;  -- Total runtime: 103 ms

These are the results from Postgres 9.0.5 on Debian Squeeze. Similar results on 8.4.

In a test with all NULL values both functions perform the same: ~37 ms.

I did some research and found an interesting gotcha. Declaring an SQL function STRICT disables function-inlining in most cases. More about that in the PostgreSQL Online Journal or in the pgsql-performance mailing list or in the Postgres Wiki.

But I am not quite sure how this could be the explanation. Not inlining the function causes a performance slump in this simple scenario? No index, no disc read, no sorting. Maybe an overhead from the repeated function call that is streamlined away by inlining the function?

Retests

Same test, same hardware, Postgres 9.1. Even bigger differences:

SELECT f_sort_array(arr)        FROM tbl;  -- Total runtime:  27 ms
SELECT f_sort_array_strict(arr) FROM tbl;  -- Total runtime: 107 ms

Same test, new hardware, Postgres 9.6. The gap is even bigger, yet:

SELECT f_sort_array(arr)        FROM tbl;  -- Total runtime:  10 ms
SELECT f_sort_array_strict(arr) FROM tbl;  -- Total runtime:  60 ms

Chaunce

These two functions are not equivalent. STRICT is not a hint but an instruction, "don't call this with null arguments". This will result in a not null check that you haven't explicitly asked for, hence comment not answer. I am however surprised that when I tested this on a table with a NOT NULL modifier, this still has the same effect. – Malcolm

@couling: The example function yields identical results with or without STRICT. "Common sense" would tell me that STRICT is faster, if NULL values are involved, but that's not the case. I added a quick test with NULLs to my question. – Chaunce

Just because null values are not involved does not mean postgres knows that they are not involved. It may still have to check. – Malcolm

Good and well researched question, why the downvote!? – Novena

The Pg developer must read this post as a bug report. BigBig loss of performance destroys any expectation of STRICT users. – Diena

Answer 1

Maybe an overhead from the repeated function call that is streamlined away by inlining the function?

That's what I'd guess. You've got a very simple expression there. An actual function-call presumably involves stack setup, passing parameters etc.

The test below gives run-times of 5ms for inlined and 50ms for strict.

BEGIN;

CREATE SCHEMA f;

SET search_path = f;

CREATE FUNCTION f1(int) RETURNS int AS $$SELECT 1$$ LANGUAGE SQL;
CREATE FUNCTION f2(int) RETURNS int AS $$SELECT 1$$ LANGUAGE SQL STRICT;

\timing on
SELECT sum(f1(i)) FROM generate_series(1,10000) i;
SELECT sum(f2(i)) FROM generate_series(1,10000) i;
\timing off

ROLLBACK;

Sleeper

Yep, STRICT functions cannot be inlined, and thus can be a lot slower especially for simple expressions. Personally I'm a little surprised Pg doens't inline them effectively as CASE WHEN input IS NULL THEN NULL ELSE func(input) END (or some simpler-to-evaluate function-like equivalent) but I'm sure it can't be that simple or they would've done it long ago. – Blondellblondelle

@CraigRinger: There is one important difference between Richard's function and mine above. STRICT changes the result of this function when called with NULL. So it is remotely understandable that it should perform slower. The lesson we learn here: don't use STRICT for simple functions unless you need it. There is definitely potential here for optimization like you say, but it is small and for simple cases only, so we may never see it happen. This should be documented. I am pretty sure most people are surprised by the effect. – Chaunce

This bug report was in 2011... And nowadays, (2019!) the bug persist... How to say to PostgreSQL development team that this is a relevant bug. No vote system? – Diena

What bug report? An optimisation not being applied in a corner case isn't a bug. A feature request, but not a bug. There is a well documented (but fairly rigorous, naturally) method for offering patches if you are inclined. – Sleeper

Answer 2

It's about function inlining like suspected and confirmed by Richard's test.

To be clear, the Postgres Wiki lists this requirement for inlining of a scalar function (like my example):

• if the function is declared STRICT, then the planner must be able to prove that the body expression necessarily returns NULL if any parameter is null. At present, this condition is only satisfied if: every parameter is referenced at least once, and all functions, operators and other constructs used in the body are themselves STRICT.

The example function obviously does not qualify. Both the CASE construct and the ARRAY constructor are to blame according to my tests.

Table functions (returning a set of rows) are more picky, yet:

• the function is not declared STRICT

If the function cannot be inlined, repeated execution collects the function overhead repeatedly. The difference in performance got bigger in later Postgres versions.

Retest with PostgreSQL 13 on a current laptop. Bigger difference, yet:

SELECT f_sort_array(arr)        FROM tbl;  -- Total runtime:   4 ms
SELECT f_sort_array_strict(arr) FROM tbl;  -- Total runtime:  32 ms

Same test on dbfiddle.com, PostgreSQL 13. Bigger difference, yet:

SELECT f_sort_array(arr)        FROM tbl;  -- Total runtime:   4 ms
SELECT f_sort_tblay_strict(arr) FROM tbl;  -- Total runtime: 137 ms (!)

Comprehensive test including tests with half and all NULL values:

db<>fiddle here

Chaunce