PostgreSQL 函数稳定性在索引与全表访问下的性能差异

一、构建测试数据

create or replace function test_volatile(id integer)
    returns bigint
    volatile
    language sql
as
$$ select count(*) from t1 $$ ;
/
 
create or replace function test_stable(id integer)
    returns bigint
    stable
    language sql
as
$$ select count(*) from t1 $$ ;
/
 
create or replace function test_immutable(id integer)
    returns bigint
    immutable
    language sql
as
$$ select count(*) from t1 $$ ;
/

insert into test select 1 from generate_series(1,1000);

二、没有索引情况下执行性能

这里的索引是指 test(id) 索引，显然，因为 id 值都是相同的，这里的索引实际意义不大。

test=# explain analyze select count(*) from test where id=test_volatile(1);
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=270.00..270.01 rows=1 width=8) (actual time=16154.566..16154.566 rows=1 loops=1)
   ->  Seq Scan on test  (cost=0.00..267.50 rows=1000 width=0) (actual time=16154.564..16154.564 rows=0 loops=1)
         Filter: (id = test_volatile(1))
         Rows Removed by Filter: 1000
 Planning Time: 0.165 ms
 Execution Time: 16154.585 ms
(6 rows)

Time: 16155.404 ms (00:16.155)
test=#
test=# explain analyze select count(*) from test where id=test_stable(1);
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=267.50..267.51 rows=1 width=8) (actual time=16401.441..16401.441 rows=1 loops=1)
   ->  Seq Scan on test  (cost=0.00..267.50 rows=1 width=0) (actual time=16401.439..16401.439 rows=0 loops=1)
         Filter: (id = test_stable(1))
         Rows Removed by Filter: 1000
 Planning Time: 28.010 ms
 Execution Time: 16401.473 ms
(6 rows)

Time: 16430.319 ms (00:16.430)

test=# explain analyze select count(*) from test where id=test_immutable(1);
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Aggregate  (cost=17.50..17.51 rows=1 width=8) (actual time=0.065..0.065 rows=1 loops=1)
   ->  Seq Scan on test  (cost=0.00..17.50 rows=1 width=0) (actual time=0.064..0.064 rows=0 loops=1)
         Filter: (id = '100000'::bigint)
         Rows Removed by Filter: 1000
 Planning Time: 44.469 ms
 Execution Time: 0.083 ms
(6 rows)

Time: 45.197 ms

三、创建索引情况下的执行性能

create index idx_test_id on test(id);

test=# explain analyze select count(*) from test where id=test_volatile(1);
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=270.00..270.01 rows=1 width=8) (actual time=16497.225..16497.226 rows=1 loops=1)
   ->  Seq Scan on test  (cost=0.00..267.50 rows=1000 width=0) (actual time=16497.223..16497.223 rows=0 loops=1)
         Filter: (id = test_volatile(1))
         Rows Removed by Filter: 1000
 Planning Time: 0.438 ms
 Execution Time: 16497.258 ms
(6 rows)

Time: 16498.229 ms (00:16.498)
test=# explain analyze select count(*) from test where id=test_stable(1);
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.55..4.56 rows=1 width=8) (actual time=17.419..17.419 rows=1 loops=1)
   ->  Index Only Scan using idx_test_id on test  (cost=0.53..4.54 rows=1 width=0) (actual time=17.417..17.417 rows=0 loops=1)
         Index Cond: (id = test_stable(1))
         Heap Fetches: 0
 Planning Time: 16.875 ms
 Execution Time: 17.511 ms
(6 rows)

Time: 34.742 ms
test=# explain analyze select count(*) from test where id=test_immutable(1);
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.30..4.31 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)
   ->  Index Only Scan using idx_test_id on test  (cost=0.28..4.29 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)
         Index Cond: (id = '100000'::bigint)
         Heap Fetches: 0
 Planning Time: 18.673 ms
 Execution Time: 0.032 ms
(6 rows)

Time: 19.042 ms

四、结论

1、对于 volatile 类型的函数，由于不同时刻函数结果可能不同，从安全角度需要逐行调用函数。

2、这里重点关注的 test_stable 函数：使用全表扫描，每行都要执行一次。使用索引，只需执行一次。