PostgreSQL 数据访问 offset 的质变 case

offset limit是一个多么常见的需求啊，但是你知道offset的数据可能隐藏着质变吗？

如图

node有30w条数据，其中前100条是满足条件的，然后100条到20w条都是不满足条件的。

所以offset 10 limit 10非常的快。

但是offset 100 limit 10，就要扫描从100到20w条记录，然后再往后才是满足条件的记录。

这就是质变的原因。

生成1000万测试记录。

更新info字段的数据，分布在前1000条和第500万后的100条。

order by id offset 100 limit 100查询的是前面的记录，非常快。

如果扫描的是1000条以后的，因为满足条件的记录是500w往后的，所以至少要扫描500万条记录才能拿到结果。

关闭seqscan则会使用索引扫描，一样的需要扫描一些不满足条件的记录。

removed by filter就是很好的说明

如果把limit加大到超过实际的满足条件的结果，则需要扫完所有的记录。

offset仅仅是偏移量，不是从此位置开始扫描，所以偏移量前的tuple都是需要被扫描到的。

limit的使用也需要注意，如果有断层产生，会额外的扫描更多的块。

offset一种好的优化方法是根据pk来位移。

例子见我以前写的一批文章。

一位开发的同事给我一个sql, 问我为什么只改了一个条件, 查询速度居然从毫秒就慢到几十秒了,

如下 :

运行结果100毫秒左右.

执行计划 :

改成如下 :

运行几十秒.

执行计划如下 :

我们看到两个sql执行计划是一样的, 但是走索引扫描的记录却千差万别. 第二个sql扫描了多少行呢?

我们来看看第二个查询得到的create_time值是多少:

结果 :

那么它扫描了多少行(或者说多少个数据块)呢? 通过explain verbose可以输出.

当然使用以下查询也可以估算出来 :

也就是说本例的sql中的where条件的数据在create_time这个字段顺序上的分布比较零散, 并且数据量比较庞大.

所以offset 10后, 走create_time这个索引自然就慢了.

仔细的了解了一下开发人员的需求, 是要做类似翻页的需求.

优化方法1,

在不新增任何索引的前提下, 还是走create_time这个索引, 减少重复扫描的数据.

需要得到每次取到的最大的create_time值, 以及可以标示这条记录的唯一id.

下次取的时候, 不要使用offset 下一页, 而是加上这两个条件.

例如 :

通过这种方法, 可以减少limit x offset y这种方法取后面的分页数据带来的大量数据块离散扫描.

以前写的一些关于分页优化的例子 :

<a href="http://blog.163.com/digoal@126/blog/static/163877040201111694355822/">http://blog.163.com/digoal@126/blog/static/163877040201111694355822/</a>

<a href="http://blog.163.com/digoal@126/blog/static/1638770402012520105855757/">http://blog.163.com/digoal@126/blog/static/1638770402012520105855757/</a>