PostgreSQL offset 原理，及使用注意事项

背景

使用PostgreSQL查询记录时，可以使用offset跳过一些记录，从跳跃点开始取后面的数据。

但是这里有一个问题是这样的:

postgres=# create or replace function f() returns void as $$  
declare  
begin  
  raise notice 'called';  
end;  
$$ language plpgsql strict volatile;  
CREATE FUNCTION  

虽然我在这里跳跃了3条记录，但是f()还是在offset的过程中被调用了。

理论上offset跳过的部分是不应该被调用的。

postgres=# select f(),* from ( values(1),(2),(3),(4),(5),(6) ) t(id) offset 3 limit 2;  
NOTICE:  called  
NOTICE:  called  
NOTICE:  called  
NOTICE:  called  
NOTICE:  called  
 f | id   
---+----  
   |  4  
   |  5  
(2 rows)  
  
postgres=# alter function f() stable;  
ALTER FUNCTION  
postgres=# select f(),* from ( values(1),(2),(3),(4),(5),(6) ) t(id) offset 3 limit 2;  
NOTICE:  called  
NOTICE:  called  
NOTICE:  called  
NOTICE:  called  
NOTICE:  called  
 f | id   
---+----  
   |  4  
   |  5  
(2 rows)  

如果使用where条件过滤的行，是绝对不会被调用的。

postgres=# select f(),* from ( values(1),(2),(3),(4),(5),(6) ) t(id) where id=1 limit 5;  
NOTICE:  called  
 f | id   
---+----  
   |  1  
(1 row)  

另外把函数改成immutable的话，优化器会把这个函数当成一个常量来处理，在生成执行计划前就常量化掉了，所以怎么样都只会调用一次。

postgres=# alter function f() immutable;  
ALTER FUNCTION  
postgres=# select f(),* from ( values(1),(2),(3),(4),(5),(6) ) t(id) offset 3 limit 2;  
NOTICE:  called  
 f | id   
---+----  
   |  4  
   |  5  
(2 rows)  

那么问题来了，offset 100000 limit 1这样的查询，会造成一个非常严重的问题，被offset的行，每次都会触发函数调用，非常的耗时。

这也给排查性能问题带来了一定的困扰。

这个问题已提交给社区，tom lane给出了回复，因为offset只是将执行结果的一部分过滤掉，不发送给客户端，实际上这些过滤掉的结果是实际发生了计算的。

No, it's not a bug.  OFFSET only results in the skipped tuples not being  
delivered to the client; it does not cause them not to be computed.  
  
You could probably do something with a two-level select with the OFFSET  
in the sub-select and the volatile function in the top level.  
  
            regards, tom lane    

如果要把计算挪出来，防止多次调用function，可以用子查询。把function 放到最外层，把offset放到子查询里面，这样offset过滤的结果就只会被扫描，而不会被用于计算。 例如

postgres=# alter function f() volatile;  
ALTER FUNCTION  
postgres=#  select f(),* from (select * from ( values(1),(2),(3),(4),(5),(6) ) t(id) offset 3 limit 2) t;  
NOTICE:  called  
NOTICE:  called  
 f | id   
---+----  
   |  4  
   |  5  
(2 rows)  

digoal’s 大量PostgreSQL文章入口