PostgreSQL FETCH WITH TIES 代替 LIMIT OFFSET 解决分页性能优化GAP问题
digoal约 6 分钟PostgreSQL fetch with ties 代替 limit offset 解决分页性能优化gap问题
作者
digoal
日期
2023-11-11
标签
PostgreSQL , PolarDB , 分页 , limit offset , fetch with ties。
背景
fetch with ties 代替 limit offset 解决分页性能优化gap问题
https://www.postgresql.org/docs/current/sql-select.html
《PostgreSQL 13 offset fetch first with ties - 返回ordered peer行S》
当使用limit offset翻页翻到很后面时, 性能会非常差, 原因是offset需要扫描大量的记录并进行过滤. 所以出现了很多优化分页的方法. 其中增加位置偏移条件最为常用, 但是掌握不好容易出现gap.
使用位置偏移条件优化分页的例子
create table t_off (id serial primary key, info text, c int, ts timestamp);
create index on t_off (c, ts); insert into t_off (info,c,ts) select md5(random()::text), random()*100, now() from generate_series(1,1000);
insert into t_off (info,c,ts) select md5(random()::text), random()*100, now() from generate_series(1,1000);
insert into t_off (info,c,ts) select md5(random()::text), random()*100, now() from generate_series(1,1000);
insert into t_off (info,c,ts) select md5(random()::text), random()*100, now() from generate_series(1,1000);
limit offset
老标准: limit , 可能出现gap
db1=> explain select * from t_off where c=1 order by ts limit 10;
QUERY PLAN
-------------------------------------------------------------------------------------
Limit (cost=0.28..7.64 rows=10 width=49)
-> Index Scan using t_off_c_ts_idx on t_off (cost=0.28..36.34 rows=49 width=49)
Index Cond: (c = 1)
(3 rows)
用于优化的ts条件, 查询完上次的记录得到的最大ts:
db1=> explain select * from t_off where c=1 and ts>'2023-11-11 00:31:57.304588' order by ts limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Limit (cost=0.28..8.51 rows=10 width=49)
-> Index Scan using t_off_c_ts_idx on t_off (cost=0.28..30.72 rows=37 width=49)
Index Cond: ((c = 1) AND (ts > '2023-11-11 00:31:57.304588'::timestamp without time zone))
(3 rows)
问题: 用于优化的ts条件有重复值, 会出现gap
db1=> select * from t_off where c=1 order by ts limit 5;
id | info | c | ts
-----+----------------------------------+---+----------------------------
16 | ce70259b8c0b5e83dca5f20cef93d1ee | 1 | 2023-11-11 00:31:57.304588
79 | 3a08bcf762a062e86318f516fddea793 | 1 | 2023-11-11 00:31:57.304588
230 | 01bc9f9c59b70311369aaa33c3cca3a7 | 1 | 2023-11-11 00:31:57.304588
257 | bb22ec8f00de73c19017479fdf981116 | 1 | 2023-11-11 00:31:57.304588
286 | 70bfb092ab95cc8c8f5aef9e2762af0c | 1 | 2023-11-11 00:31:57.304588
(5 rows) db1=> select * from t_off where c=1 order by ts limit 10;
id | info | c | ts
-----+----------------------------------+---+----------------------------
16 | ce70259b8c0b5e83dca5f20cef93d1ee | 1 | 2023-11-11 00:31:57.304588
79 | 3a08bcf762a062e86318f516fddea793 | 1 | 2023-11-11 00:31:57.304588
230 | 01bc9f9c59b70311369aaa33c3cca3a7 | 1 | 2023-11-11 00:31:57.304588
257 | bb22ec8f00de73c19017479fdf981116 | 1 | 2023-11-11 00:31:57.304588
286 | 70bfb092ab95cc8c8f5aef9e2762af0c | 1 | 2023-11-11 00:31:57.304588
311 | c3b8f1eab66fe88cfd59e92d039fbaa6 | 1 | 2023-11-11 00:31:57.304588
452 | f1c000b8a200207c52b6c3f9a8babe72 | 1 | 2023-11-11 00:31:57.304588
470 | 7b4702f5a4ff75ef237cbf7af5b8d082 | 1 | 2023-11-11 00:31:57.304588
642 | 82d641bcf235eba92a9368f0ac553dca | 1 | 2023-11-11 00:31:57.304588
749 | 669488263cfcec928ac2693995309eea | 1 | 2023-11-11 00:31:57.304588
(10 rows)
db1=> select * from t_off where c=1 and ts>'2023-11-11 00:31:57.304588' order by ts limit 5;
id | info | c | ts
------+----------------------------------+---+----------------------------
1017 | e12d1088e80136e869cf82816b28ab51 | 1 | 2023-11-11 00:31:57.313518
1058 | 39dc148d9223bcaddcdb2096750e513b | 1 | 2023-11-11 00:31:57.313518
1070 | 31c83c561119140721f94e8b28669914 | 1 | 2023-11-11 00:31:57.313518
1102 | ee8f076e2e29299ace4c5fb71ddf9dda | 1 | 2023-11-11 00:31:57.313518
1148 | 37232358c5995513c19545b8232aced1 | 1 | 2023-11-11 00:31:57.313518
(5 rows)
fetch with ties
新标准: limit 改成 fetch , 返回可以根据需要超过Limit数, 把最后一条相同的ts都返回. 避免了翻页优化带来的gap. 老标准(limit)比较麻烦, 需要引入pk或uk来解决.
限制10条, 但是实际上有14条ts一样的都返回了.
db1=> select * from t_off where c=1 order by ts fetch first 10 row with ties;
id | info | c | ts
-----+----------------------------------+---+----------------------------
16 | ce70259b8c0b5e83dca5f20cef93d1ee | 1 | 2023-11-11 00:31:57.304588
79 | 3a08bcf762a062e86318f516fddea793 | 1 | 2023-11-11 00:31:57.304588
230 | 01bc9f9c59b70311369aaa33c3cca3a7 | 1 | 2023-11-11 00:31:57.304588
257 | bb22ec8f00de73c19017479fdf981116 | 1 | 2023-11-11 00:31:57.304588
286 | 70bfb092ab95cc8c8f5aef9e2762af0c | 1 | 2023-11-11 00:31:57.304588
311 | c3b8f1eab66fe88cfd59e92d039fbaa6 | 1 | 2023-11-11 00:31:57.304588
452 | f1c000b8a200207c52b6c3f9a8babe72 | 1 | 2023-11-11 00:31:57.304588
470 | 7b4702f5a4ff75ef237cbf7af5b8d082 | 1 | 2023-11-11 00:31:57.304588
642 | 82d641bcf235eba92a9368f0ac553dca | 1 | 2023-11-11 00:31:57.304588
749 | 669488263cfcec928ac2693995309eea | 1 | 2023-11-11 00:31:57.304588
816 | 9a92daa3c8ceb90e023d127485c4a053 | 1 | 2023-11-11 00:31:57.304588
827 | 82abd37368d66eb03347429ff4b28e9e | 1 | 2023-11-11 00:31:57.304588
846 | 48f9737b18bf3e8dae7531a1b7fa4d45 | 1 | 2023-11-11 00:31:57.304588
990 | f7885a8e585ae7fa0c4b73dd356f260b | 1 | 2023-11-11 00:31:57.304588
(14 rows)
此时gap就没有了.
db1=> select * from t_off where c=1 and ts>'2023-11-11 00:31:57.304588' order by ts fetch first 10 row with ties;
id | info | c | ts
------+----------------------------------+---+----------------------------
1017 | e12d1088e80136e869cf82816b28ab51 | 1 | 2023-11-11 00:31:57.313518
1058 | 39dc148d9223bcaddcdb2096750e513b | 1 | 2023-11-11 00:31:57.313518
1070 | 31c83c561119140721f94e8b28669914 | 1 | 2023-11-11 00:31:57.313518
1102 | ee8f076e2e29299ace4c5fb71ddf9dda | 1 | 2023-11-11 00:31:57.313518
1148 | 37232358c5995513c19545b8232aced1 | 1 | 2023-11-11 00:31:57.313518
1425 | df4ff8d17227012f240b5ecee69d5758 | 1 | 2023-11-11 00:31:57.313518
1654 | c0bb3ebf9eda3a3a0228dd7784eccbfe | 1 | 2023-11-11 00:31:57.313518
1704 | 70283bbc87968f25aed18524030e1a21 | 1 | 2023-11-11 00:31:57.313518
1737 | 7c1b782104144f3cbc97e247e5572165 | 1 | 2023-11-11 00:31:57.313518
1779 | 8af082c81acc99fa2f8afdffd58f4d11 | 1 | 2023-11-11 00:31:57.313518
1861 | 0c9ecc39655bf834d1cdc07cf09ed362 | 1 | 2023-11-11 00:31:57.313518
(11 rows)
新标准非常适合翻页优化
db1=> explain select * from t_off where c=1 order by ts fetch first 10 row with ties;
QUERY PLAN
-------------------------------------------------------------------------------------
Limit (cost=0.28..7.64 rows=10 width=49)
-> Index Scan using t_off_c_ts_idx on t_off (cost=0.28..36.34 rows=49 width=49)
Index Cond: (c = 1)
(3 rows) db1=> explain select * from t_off where c=1 and ts>'2023-11-11 00:31:57.304588' order by ts fetch first 10 row with ties;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Limit (cost=0.28..8.51 rows=10 width=49)
-> Index Scan using t_off_c_ts_idx on t_off (cost=0.28..30.72 rows=37 width=49)
Index Cond: ((c = 1) AND (ts > '2023-11-11 00:31:57.304588'::timestamp without time zone))
(3 rows)