The Postgres 文档 http://www.postgresql.org/docs/9.2/static/xfunc-volatility.html say
为了获得最佳优化结果,您应该使用对其有效的最严格的波动性类别来标记您的函数。
然而,我似乎有一个例子,情况并非如此,我想了解发生了什么。 (背景:我正在运行 postgres 9.2)
我经常需要将以整数秒表示的时间转换为日期。我写了一个函数来执行此操作:
CREATE OR REPLACE FUNCTION
to_datestamp(time_int double precision) RETURNS date AS $$
SELECT date_trunc('day', to_timestamp($1))::date;
$$ LANGUAGE SQL;
让我们将波动性设置为 IMMUTABLE 和 STABLE 的情况下与其他相同函数的性能进行比较:
CREATE OR REPLACE FUNCTION
to_datestamp_immutable(time_int double precision) RETURNS date AS $$
SELECT date_trunc('day', to_timestamp($1))::date;
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION
to_datestamp_stable(time_int double precision) RETURNS date AS $$
SELECT date_trunc('day', to_timestamp($1))::date;
$$ LANGUAGE SQL STABLE;
为了测试这一点,我将创建一个包含 10^6 个随机整数的表,对应于 2010-01-01 和 2015-01-01 之间的时间
CREATE TEMPORARY TABLE random_times AS
SELECT 1262304000 + round(random() * 157766400) AS time_int
FROM generate_series(1, 1000000) x;
最后,我将调用该表上的两个函数;在我的特定机器上,原始版本需要约 6 秒,不可变版本需要约 33 秒,稳定版本需要约 6 秒。
EXPLAIN ANALYZE SELECT to_datestamp(time_int) FROM random_times;
Seq Scan on random_times (cost=0.00..20996.62 rows=946950 width=8)
(actual time=0.150..5493.722 rows=1000000 loops=1)
Total runtime: 6258.827 ms
EXPLAIN ANALYZE SELECT to_datestamp_immutable(time_int) FROM random_times;
Seq Scan on random_times (cost=0.00..250632.00 rows=946950 width=8)
(actual time=0.211..32209.964 rows=1000000 loops=1)
Total runtime: 33060.918 ms
EXPLAIN ANALYZE SELECT to_datestamp_stable(time_int) FROM random_times;
Seq Scan on random_times (cost=0.00..20996.62 rows=946950 width=8)
(actual time=0.086..5295.608 rows=1000000 loops=1)
Total runtime: 6063.498 ms
这里发生了什么?例如,postgres 是否花费时间缓存结果,而这实际上并没有帮助,因为传递给函数的参数不太可能重复?
(我正在运行 postgres 9.2。)
Thanks!
UPDATE
谢谢克雷格·林格 https://stackoverflow.com/users/398670/craig-ringer这已经在pgsql-性能邮件列表 http://www.postgresql.org/message-id/520AD240.9060508@2ndquadrant.com。强调:
汤姆·莱恩 说 http://www.postgresql.org/message-id/22812.1376453860@sss.pgh.pa.us
[耸耸肩...]使用 IMMUTABLE 来掩盖函数的可变性
(在本例中为 date_trunc)是一个坏主意。很可能会导致错误
答案,不用担心性能问题。在这个特殊的例子中,我
想象性能问题来自于抑制该选项
内联函数体......但你应该更担心
在其他情况下您是否没有得到完全虚假的答案。
帕维尔·斯特胡勒 说 http://www.postgresql.org/message-id/CAFj8pRBF3Qr7WtQwO1H_WN=hhFGk0semwhdE+ODz3iyv-TroMQ@mail.gmail.com
如果我理解的话,使用的 IMMUTABLE 标志会禁用内联。你所看到的,是
SQL 评估溢出。我的规则是 - 如果可能的话,不要在 SQL 函数中使用标志。