PostgreSQL 数据访问 offset 的质变 case
背景
offset limit是一个多么常见的需求啊，但是你知道offset的数据可能隐藏着质变吗？
如图
node有30W条数据，其中前100条是满足条件的，然后100条到20W条都是不满足条件的。
所以offset 10 limit 10非常的快。
但是offset 100 limit 10，就要扫描从100到20W条记录，然后再往后才是满足条件的记录。
这就是质变的原因。
例子
生成1000万测试记录。
postgres=# create table tbl(id int primary key, info text);
CREATE TABLE
postgres=# insert into tbl select generate_series(1,10000000),'';
INSERT 0 10000000
更新info字段的数据，分布在前1000条和第500万后的100条。
postgres=# update tbl set info='test' where id<1000 or id between 5000000 and 5000100;
UPDATE 1100
order by id offset 100 limit 100查询的是前面的记录，非常快。
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where info='test' order by id offset 100 limit 100;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=49339.42..98678.40 rows=100 width=5) (actual time=0.154..0.343 rows=100 loops=1)
Output: id, info
Buffers: shared hit=603
-> Index Scan using tbl_pkey on public.tbl (cost=0.43..329091.45 rows=667 width=5) (actual time=0.019..0.293 rows=200 loops=1)
Output: id, info
Filter: (tbl.info = 'test'::text)
Buffers: shared hit=603
Planning time: 0.253 ms
Execution time: 0.386 ms
(9 rows)
如果扫描的是1000条以后的，因为满足条件的记录是500W往后的，所以至少要扫描500万条记录才能拿到结果。
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where info='test' order by id offset 1000 limit 100;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Limit (cost=169291.40..169291.40 rows=1 width=5) (actual time=952.266..952.330 rows=100 loops=1)
Output: id, info
Buffers: shared hit=44260
-> Sort (cost=169289.74..169291.40 rows=667 width=5) (actual time=951.892..952.102 rows=1100 loops=1)
Output: id, info
Sort Key: tbl.id
Sort Method: quicksort Memory: 100kB
Buffers: shared hit=44260
-> Seq Scan on public.tbl (cost=0.00..169258.45 rows=667 width=5) (actual time=951.167..951.496 rows=1100 loops=1)
Output: id, info
Filter: (tbl.info = 'test'::text)
Rows Removed by Filter: 9998900
Buffers: shared hit=44260
Planning time: 0.105 ms
Execution time: 952.375 ms
(15 rows)
关闭seqscan则会使用索引扫描，一样的需要扫描一些不满足条件的记录。
removed by filter就是很好的说明
postgres=# set enable_seqscan=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where info='test' order by id offset 1000 limit 100;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=329091.45..329091.45 rows=1 width=5) (actual time=888.400..888.519 rows=100 loops=1)
Output: id, info
Buffers: shared hit=38991
-> Index Scan using tbl_pkey on public.tbl (cost=0.43..329091.45 rows=667 width=5) (actual time=0.033..888.267 rows=1100 loops=1)
Output: id, info
Filter: (tbl.info = 'test'::text)
Rows Removed by Filter: 4999000
Buffers: shared hit=38991
Planning time: 0.110 ms
Execution time: 888.632 ms
(10 rows)
or
postgres=# set enable_seqscan=on;
SET
postgres=# set enable_sort=off;
SET
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where info='test' order by id offset 1000 limit 100;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=329091.45..329091.45 rows=1 width=5) (actual time=887.791..887.906 rows=100 loops=1)
Output: id, info
Buffers: shared hit=38991
-> Index Scan using tbl_pkey on public.tbl (cost=0.43..329091.45 rows=667 width=5) (actual time=0.040..887.540 rows=1100 loops=1)
Output: id, info
Filter: (tbl.info = 'test'::text)
Rows Removed by Filter: 4999000
Buffers: shared hit=38991
Planning time: 0.154 ms
Execution time: 887.964 ms
(10 rows)
如果把limit加大到超过实际的满足条件的结果，则需要扫完所有的记录。
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where info='test' order by id offset 1000 limit 10000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=329091.45..329091.45 rows=1 width=5) (actual time=898.675..1786.476 rows=100 loops=1)
Output: id, info
Buffers: shared hit=74776
-> Index Scan using tbl_pkey on public.tbl (cost=0.43..329091.45 rows=667 width=5) (actual time=0.030..1786.240 rows=1100 loops=1)
Output: id, info
Filter: (tbl.info = 'test'::text)
Rows Removed by Filter: 9998900
Buffers: shared hit=74776
Planning time: 0.110 ms
Execution time: 1786.536 ms
(10 rows)
小结
1. offset仅仅是偏移量，不是从此位置开始扫描，所以偏移量前的tuple都是需要被扫描到的。
2. limit的使用也需要注意，如果有断层产生，会额外的扫描更多的块。
3. offset一种好的优化方法是根据PK来位移。
例子见我以前写的一批文章。
分页优化手段之一
一位开发的同事给我一个SQL, 问我为什么只改了一个条件, 查询速度居然从毫秒就慢到几十秒了,
如下 :
SELECT *
FROM tbl
where create_time>='2014-02-08' and create_time<'2014-02-11'
and x=3
and id != '123'
and id != '321'
and y > 0 order by create_time limit 1 offset 0;
运行结果100毫秒左右.
执行计划 :
Limit (cost=0.56..506.19 rows=1 width=1038)
-> Index Scan using idx on tbl (cost=0.56..2381495.60 rows=4710 width=1038)
Index Cond: ((create_time >= '2014-02-08 00:00:00'::timestamp without time zone) AND (create_time < '2014-02-11 00:00:00'::timestamp without time zone))
Filter: (((id)::text <> '123'::text) AND ((id)::text <> '321'::text) AND (y > 0) AND (x = 3))
改成如下 :
SELECT *
FROM tbl
where create_time>='2014-02-08' and create_time<'2014-02-11'
and x=3
and id != '123'
and id != '321'
and y > 0 order by create_time limit 1 offset 10;
运行几十秒.
执行计划如下 :
Limit (cost=5056.98..5562.62 rows=1 width=1038)
-> Index Scan using idx on tbl (cost=0.56..2382076.78 rows=4711 width=1038)
Index Cond: ((create_time >= '2014-02-08 00:00:00'::timestamp without time zone) AND (create_time < '2014-02-11 00:00:00'::timestamp without time zone))
Filter: (((id)::text <> '11622'::text) AND ((id)::text <> '13042'::text) AND (y > 0) AND (x = 3))
我们看到两个SQL执行计划是一样的, 但是走索引扫描的记录却千差万别. 第二个SQL扫描了多少行呢?
我们来看看第二个查询得到的create_time值是多少:
select create_time from tbl
where create_time>='2014-02-08' and create_time<'2014-02-11'
and x=3
and id != '123'
and id != '321'
and y > 0 order by create_time limit 1 offset 10;
结果 :
'2014-02-08 18:38:35.79'
那么它扫描了多少行(或者说多少个数据块)呢? 通过explain verbose可以输出.
当然使用以下查询也可以估算出来 :
select count(*) from tbl where create_time<='2014-02-08 18:38:35.79' and create_time>='2014-02-08';
count
---------
1448081
(1 row)
也就是说本例的SQL中的WHERE条件的数据在create_time这个字段顺序上的分布比较零散, 并且数据量比较庞大.
所以offset 10后, 走create_time这个索引自然就慢了.
仔细的了解了一下开发人员的需求, 是要做类似翻页的需求.
优化方法1,
在不新增任何索引的前提下, 还是走create_time这个索引, 减少重复扫描的数据.
需要得到每次取到的最大的create_time值, 以及可以标示这条记录的唯一ID.
下次取的时候, 不要使用offset 下一页, 而是加上这两个条件.
例如 :
select create_time from tbl
where create_time>='2014-02-08' and create_time<'2014-02-11'
and x=3
and id != '123'
and id != '321'
and pk not in (?) -- 这个ID是上次取到的create_time最大的值的所有记录的pk值.
and y > 0
and create_time >= '2014-02-08 18:38:35.79' -- 这个时间是上次取到的数据的最大的时间值.
order by create_time limit ? offset 0;
如果偏移量本来就是一个PK，则不需要加pk not in (?)的条件
通过这种方法, 可以减少limit x offset y这种方法取后面的分页数据带来的大量数据块离散扫描.
以前写的一些关于分页优化的例子 :
http://blog.163.com/digoal@126/blog/static/163877040201111694355822/
http://blog.163.com/digoal@126/blog/static/1638770402012520105855757/