performance - postgres 函数 : when does IMMUTABLE hurt performance?

Question

Postgres docs说

For best optimization results, you should label your functions with the strictest volatility category that is valid for them.

但是，我似乎有一个不是这种情况的例子，我想了解发生了什么。 (背景:我正在运行 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。)

谢谢!

更新

感谢Craig Ringer这已在 pgsql-performance mailing list 上讨论过.亮点:

Tom Lane says

[ shrug... ] Using IMMUTABLE to lie about the mutability of a function (in this case, date_trunc) is a bad idea. It's likely to lead to wrong answers, never mind performance issues. In this particular case, I imagine the performance problem comes from having suppressed the option to inline the function body ... but you should be more worried about whether you aren't getting flat-out bogus answers in other cases.

Pavel Stehule says

If I understand, a used IMMUTABLE flag disables inlining. What you see, is SQL eval overflow. My rule is - don't use flags in SQL functions, when it is possible.

Answer 1

问题是 to_timestamp 返回带时区的时间戳。如果将 to_timestamp 函数替换为没有时区的“手动”计算，则性能没有差异

create or replace function to_datestamp_stable(
    time_int double precision
) returns date as $$
  select date_trunc('day', timestamp 'epoch' + $1 * interval '1 second')::date;
$$ language sql stable;

explain analyze
select to_datestamp_stable(a)
from generate_series(1, 1000000) s (a);
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series s  (cost=0.00..22.50 rows=1000 width=4) (actual time=96.962..433.562 rows=1000000 loops=1)
 Total runtime: 459.531 ms

create or replace function to_datestamp_immutable(
    time_int double precision
) returns date as $$
  select date_trunc('day', timestamp 'epoch' + $1 * interval '1 second')::date;
$$ language sql immutable;

explain analyze
select to_datestamp_immutable(a)
from generate_series(1, 1000000) s (a);
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series s  (cost=0.00..22.50 rows=1000 width=4) (actual time=94.188..433.492 rows=1000000 loops=1)
 Total runtime: 459.434 ms

使用 to_timestamp 的相同功能

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;

explain analyze
select to_datestamp_stable(a)
from generate_series(1, 1000000) s (a);
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series s  (cost=0.00..20.00 rows=1000 width=4) (actual time=91.924..3059.570 rows=1000000 loops=1)
 Total runtime: 3103.655 ms

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;

explain analyze
select to_datestamp_immutable(a)
from generate_series(1, 1000000) s (a);
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series s  (cost=0.00..262.50 rows=1000 width=4) (actual time=92.639..20083.920 rows=1000000 loops=1)
 Total runtime: 20149.311 ms