作者
digoal
日期
2016-04-02
标签
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)
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
