问题描述
我想知道当将简单的SQL函数声明为 STRICT 时性能会下降吗？在时，我偶然发现了这种现象。
I wonder about a slump in performance when a simple SQL function is declared STRICT. I stumbled upon this phenomenon while answering a question here.
为了演示效果，我创建了一个简单SQL函数的两个变体，该函数以升序对数组的两个元素进行排序。
To demonstrate the effect I create two variants of a simple SQL function that orders two elements of an array in ascending order.
-- temporary table with 10000 random pairs of integer
CREATE TEMP TABLE arr (i int[]);
INSERT INTO arr
SELECT ARRAY[(random() * 1000)::int, (random() * 1000)::int]
FROM generate_series(1,10000);
具有 STRICT 修饰符的函数：
CREATE OR REPLACE FUNCTION f_sort_array1(int[]) RETURNS int[] AS
$$
SELECT CASE WHEN $1[1] > $1[2] THEN ARRAY[$1[2], $1[1]] ELSE $1 END;
$$ LANGUAGE sql STRICT IMMUTABLE;
不带 STRICT 修饰符的函数（否则相同） ：
Function without STRICT modifier (otherwise identical):
CREATE OR REPLACE FUNCTION f_sort_array2(int[]) RETURNS int[] AS
$$
SELECT CASE WHEN $1[1] > $1[2] THEN ARRAY[$1[2], $1[1]] ELSE $1 END;
$$ LANGUAGE sql IMMUTABLE;
结果
我每个执行大约20次次并从 EXPLAIN ANALYZE 中获得最佳结果。
SELECT f_sort_array1(i) FROM arr; -- Total runtime: 103 ms
SELECT f_sort_array2(i) FROM arr; -- Total runtime: 43 ms (!!!)
这些是v9.0.5的结果Debian Squeeze上的服务器。 v8.4上的结果相似。没有在9.1上进行测试，现在没有可用的集群。 （有人可以为v9.1提供其他结果吗？）
These are the results from a v9.0.5 server on Debian Squeeze. Similar results on v8.4. Did not test on 9.1, have no cluster at my disposal right now. (Can someone supply additional results for v9.1?)
编辑：
在具有10000个NULL值的测试中，两个函数在相同的测试环境中执行相同的操作：〜37毫秒。
In a test with 10000 NULL values both functions perform the same in the same test environment: ~37 ms.
我做了一些研究，发现了一个有趣的陷阱。在大多数情况下，声明SQL函数 STRICT会禁用函数内联。有关详细信息，请参见或在或在中。
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. How can not inlining the function cause the 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? Can you explain it? Or am I missing something?
在与PostgreSQL 9.1相同的硬件上进行的相同测试发现了更大的差异：
The same test on the same hardware with PostgreSQL 9.1 an found even bigger differences:
SELECT f_sort_array1(i) FROM arr; -- Total runtime: 107 ms
SELECT f_sort_array2(i) FROM arr; -- Total runtime: 27 ms (!!!)
使用Postgres 9.6重新测试
使用PostgreSQL 9.6在不同的硬件上进行相同的测试。差距甚至更大：
Retest with Postgres 9.6
The same test on different hardware with PostgreSQL 9.6. The gap is even bigger, yet:
SELECT f_sort_array1(i) FROM arr; -- Total runtime: 60 ms
SELECT f_sort_array2(i) FROM arr; -- Total runtime: 10 ms (!!!)
推荐答案
我猜。您在这里有一个非常简单的表达。实际的函数调用大概涉及堆栈设置，传递参数等。
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.
下面的测试对内联的运行时间为5毫秒，对于严格的运行时间为50毫秒。
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;
这篇关于没有STRICT修饰符的函数执行速度更快？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！