分页和过滤在PostgreSQL中很大的表上（键集分页？）

Question

我有一个科学数据库，目前有4,300,000条记录。这是一个科学数据库，并且有API为其提供数据。到2020年6月，我可能会拥有大约1亿条记录。

这是表“输出”的de布局：

ID   | sensor_ID    | speed    | velocity | direction
-----------------------------------------------------
1    | 1            | 10       | 1        | up
2    | 2            | 12       | 2        | up
3    | 2            | 11.5     | 1.5      | down
4    | 1            | 9.5      | 0.8      | down
5    | 3            | 11       | 0.75     | up
...

顺便说一句，这是伪数据。但是输出是一个包含5列的表格：ID，sensor_ID，速度，速度和方向。

我要实现的是一种 不错的
分页和过滤方法。我想创建一个网站（在nodejs中），其中将显示此+4,000,000条记录（目前），每页10,000条记录。我还希望能够过滤sensor_ID，速度，速度或方向。

现在，我有这个查询来选择特定的行：

SELECT * FROM output ORDER BY ID DESC OFFSET 0 LIMIT 10000 // first 10,000 rows

SELECT * FROM output ORDER BY ID DESC OFFSET 10000 LIMIT 10000 // next 10,000 rows

...

我正在搜索有关创建体面分页方法的一些信息/提示。 目前
，它的运行方式仍然非常安静，但是我认为当我们达到+50,000,000条记录时，它的运行速度会慢很多。

首先，我找到了此页面：https : //www.citusdata.com/blog/2016/03/30/five-ways-to-
paginate/。我对 按键 分页感兴趣。但老实说，我不知道如何开始。

我 认为 我必须做的是：

在ID字段上创建索引：

CREATE UNIQUE INDEX index_id ON output USING btree (ID)

我也找到了此页面：https :
//leopard.in.ua/2014/10/11/postgresql-
paginattion。当您向下滚动到“改进＃2：搜索方法”时，您可以看到他们放弃了OFFSET子句，并且正在使用WHERE子句。我还看到他们在查询中使用了
最后一个插入ID ：

SELECT * FROM output WHERE ID < <last_insert_id_here> ORDER BY ID DESC LIMIT 10000

我对此并不完全了解。对于第一页，我需要最后一个插入ID。然后，我获取了10,000条最新记录。但是在那之后，要获得第二页，我不需要最后一个插入ID，我需要第10,000个最后一个插入ID（我想）。

有人可以快速给我关于分页和过滤的很好的解释。

我正在使用的东西：-postgresql-pgadmin（用于数据库管理）-node.js（最新版本）

谢谢大家！祝您2020年愉快！

编辑1：
我不知道，但是MassiveJS（https://massivejs.org/）可以很好地使用吗？我应该在所有查询上使用它，还是只在分页查询上使用它？

编辑2： 我认为我明白了一点（如果我错了，请纠正我）。

假设我有100,000条记录：

1）获取最后插入的ID

2）使用此最后插入的ID提取最后10,000条记录

SELECT * FROM output WHERE ID < 100000 ORDER BY ID DESC LIMIT 10000 // last insert ID is here 100,000 because I have 100,000 records

3）显示10,000条记录，但还保存10,000条记录的插入ID，以在下一个查询中使用

4）获取具有新的最后插入ID的下一个10,000条记录

SELECT * FROM output WHERE ID < 90000 ORDER BY ID DESC LIMIT 10000 // 90,000 is the very last insert id - 10,000

5）…

这样对吗？

Answer 1

这是我的处理方式。对于我获取的第一页，我使用

SELECT id, col, col, col 
  FROM output 
 ORDER BY id DESC
 LIMIT 10000

然后，在我的客户端程序（node.js）中，id从结果集的最后一行捕获值。当我需要下一页时，请执行此操作。

 SELECT id, col, col, col
   FROM output
  WHERE id < my_captured_id_value
  ORDER BY id DESC

这利用了索引。即使您从表中删除了一些行，它也能正常工作。

顺便说一句，如果您的第一个分页页面具有最大的ID，则您可能希望使用降序索引。 CREATE UNIQUE INDEX index_id ON output USING btree (ID DESC)。

专业提示 SELECT *对大型数据库的性能有害。始终列出您实际需要的列。