主页 / dba / 问题 / 306701
Accepted
Tom
Asked: 2022-01-28 12:00:27 +0800 CST

PostgreSQL 中报表的查询性能

  • 772

我使用 PostgreSQL 12 来存储我的原始数据，并使用 ElasticSearch 作为报告的数据仓库。

碰巧在某些情况下JOIN（例如，数据存在于一个表中但不存在于另一个表中）ElasticSearch 不参与我，不得不直接在 PostgreSQL 中进行返回大量结果的查询。

许多年前，我使用SELECT <column1, column2> FROM <table> WHERE <conditions>;完全没有LIMITor OFFSET，这导致我的数据库负载很高，占用大量内存来返回结果，并且我的 Web 服务器无法处理返回的数据量，崩溃（内存例如限制溢出）。

为了解决这个问题，我开始使用分页系统，引入有限数量的记录，并使用 LIMIT 和条件对结果进行分页，以显示上一个查询的最后一条记录下方的记录。

例子：

-- 1st Query
SELECT <column1, column2> FROM <table> WHERE <conditions> LIMIT 512;
-- 2nd Query
SELECT <column1, column2> FROM <table> WHERE <conditions> AND id < last_id_query_1 LIMIT 512;
-- 3rd Query
SELECT <column1, column2> FROM <table> WHERE <conditions> AND id < last_id_query_2 LIMIT 512;

在我看来，这种方法似乎不是很好。阅读CURSORS它似乎做了类似的事情，但只使用一个搜索。一些网站表示它的性能比LIMIT/OFFSET.

这种说法是真的还是我现在的工作方式也不错？

由于我无法EXPLAIN ANALYZE在每个FETCH中执行CURSOR，我无法实际分析它是否更快并且具有更好的性能。

postgresql postgresql-performance

1 个回答

  1. Best Answer

    您当前正在做的是键集分页（除非您没有显示必要的 ORDER BY 以使其正常工作——我假设它确实存在）。对于许多查询和适当的索引，这可以很好地工作。你提到了一些关于它的担忧，但没有关于这些担忧的任何细节，很难知道它们的有效性和/或可解决性。

    当有大量页面时，光标和键集分页应该比 OFFSET/LIIMT 更有效，但请注意，在您的示例中没有 OFFSET。从哪里开始只有一个 LIMIT 和一个键集。换句话说，OFFSET/LIMIT 可能不好，但这不是你正在做的。

    键集分页优于服务器端游标分页的好处是状态完全由客户端有效地管理。如果客户端两周（或永远）没有返回下一页，则服务器不关心。但是，对于服务器端游标，您需要一些机制来保持在页面访问之间打开的数据库连接，并重新连接到它。您还需要一些机制来声明一个游标被放弃，以免它们积累并消耗无限量的资源。

    auto_explain 仍然会记录游标的计划，只要它通过显式 CLOSE 或通过 COMMIT 完全关闭。

    • 3

相关问题