PostgreSQL 中報表的查詢性能

我使用 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，我無法實際分析它是否更快並且具有更好的性能。

您目前正在做的是鍵集分頁（除非您沒有顯示必要的 ORDER BY 以使其正常工作——我假設它確實存在）。對於許多查詢和適當的索引，這可以很好地工作。你提到了一些關於它的擔憂，但沒有關於這些擔憂的任何細節，很難知道它們的有效性和/或可解決性。

當有大量頁面時，游標和鍵集分頁應該比 OFFSET/LIIMT 更有效，但請注意，在您的範例中沒有 OFFSET。從哪裡開始只有一個 LIMIT 和一個鍵集。換句話說，OFFSET/LIMIT 可能不好，但這不是你正在做的。

鍵集分頁優於伺服器端游標分頁的好處是狀態完全由客戶端有效地管理。如果客戶端兩週（或永遠）沒有返回下一頁，則伺服器不關心。但是，對於伺服器端游標，您需要一些機制來保持在頁面訪問之間打開的數據庫連接，並重新連接到它。您還需要一些機制來聲明一個游標被放棄，以免它們積累並消耗無限量的資源。

auto_explain 仍然會記錄游標的計劃，只要它通過顯式 CLOSE 或通過 COMMIT 完全關閉。

引用自：https://dba.stackexchange.com/questions/306701