大表的高效分頁

使用PostgreSQL 10.5。我正在嘗試創建一個分頁系統，使用者可以在其中來回切換各種結果。

為了不使用OFFSET，我將id上一頁最後一行的 傳遞到一個名為p(prevId) 的參數中。id然後我選擇高於p參數中傳遞的數字的前三行。（如本文所述）

例如，如果id上一頁的最後一行是 5，我會選擇前 3 行，其中 anid大於 5：

SELECT 
 id, 
 firstname, 
 lastname 
FROM 
 people 
WHERE 
 firstname = 'John'
 AND id > 5 
ORDER BY 
 ID ASC 
LIMIT 
 3;

這很好用，而且時機也不是很糟糕：

Limit  (cost=0.00..3.37 rows=3 width=17) (actual time=0.046..0.117 rows=3 loops=1)
  ->  Seq Scan on people  (cost=0.00..4494.15 rows=4000 width=17) (actual time=0.044..0.114 rows=3 loops=1)
        Filter: ((id > 5) AND (firstname = 'John'::text))
        Rows Removed by Filter: 384
Planning time: 0.148 ms
Execution time: 0.147 ms

但是，另一方面，如果使用者想要返回上一頁，情況看起來有點不同：

首先，我將傳遞id第一行的值，然後在它前面加上減號，表示我應該選擇參數id小於（正）p參數的行。即，如果id第一行的 為 6，則p參數為-6。同樣，我的查詢如下所示：

SELECT 
 * 
FROM 
 (
   SELECT 
     id, 
     firstname, 
     lastname 
   FROM 
     people 
   WHERE 
     firstname = 'John' 
     AND id < 6 
   ORDER BY 
     id DESC 
   LIMIT 
     3
 ) as d 
ORDER BY 
 id ASC;

id在上面的查詢中，我首先選擇小於 6的最後 3 行，然後將它們反轉，以便以與開頭描述的第一個查詢相同的方式呈現它們。

這可以正常工作，但是由於數據庫幾乎遍歷了我的所有行，因此性能受到了影響：

Sort  (cost=4252.75..4252.76 rows=1 width=17) (actual time=194.464..194.464 rows=0 loops=1)
  Sort Key: people.id
  Sort Method: quicksort  Memory: 25kB
  ->  Limit  (cost=4252.73..4252.73 rows=1 width=17) (actual time=194.460..194.460 rows=0 loops=1)
        ->  Sort  (cost=4252.73..4252.73 rows=1 width=17) (actual time=194.459..194.459 rows=0 loops=1)
              Sort Key: people.id DESC
              Sort Method: quicksort  Memory: 25kB
              ->  Gather  (cost=1000.00..4252.72 rows=1 width=17) (actual time=194.448..212.010 rows=0 loops=1)
                    Workers Planned: 1
                    Workers Launched: 1
                    ->  Parallel Seq Scan on people  (cost=0.00..3252.62 rows=1 width=17) (actual time=18.132..18.132 rows=0 loops=2)
                          Filter: ((id < 13) AND (firstname = 'John'::text))
                          Rows Removed by Filter: 100505
Planning time: 0.116 ms
Execution time: 212.057 ms

話雖如此，我很感激您花時間閱讀到這裡，我的問題是，我怎樣才能使分頁更有效率？

性能的關鍵是以下形式的匹配多列索引：

CREATE UNIQUE INDEX ON people (firstname, id);

UNIQUE，因為沒有它，排序順序可能會模棱兩可。或約束
也 可以。UNIQUE``PRIMARY KEY

雖然像您的範例一樣檢查第一列是否相等（或按與查詢相同的方向排序），但該索引適用於 paging up 和 down，儘管它更適合 paging up。

有了索引（並且在ANALYZE表上執行之後），您將不會再看到順序掃描（除非您的表很小）。數據庫不再“遍歷幾乎所有行”。

閱讀您連結到的 Markus Winand的精彩展示。

如果要跨多個分頁firstname，請使用 ROW 值。向下翻頁範例：

SELECT *
FROM  (
  SELECT id, firstname, lastname 
  FROM   people
  WHERE  (firstname, id) < ('John', 6)  -- ROW values!
  ORDER  BY firstname DESC, id DESC 
  LIMIT  3
  ) d 
ORDER BY firstname, id;

有關的：

• 在大表上使用 OFFSET 優化查詢

如果SELECT列表僅像您的範例中lastname那樣添加，您可能會嘗試將該列添加到索引中以從中獲取僅索引掃描：

CREATE UNIQUE INDEX ON people (firstname, id, lastname);

按此順序索引表達式。

在Postgres 11中，改為考慮**INCLUDE**列，使索引更小，性能更好，適用於更多情況。像：

CREATE UNIQUE INDEX ON people (firstname, id) INCLUDE (lastname);

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