複雜查詢的 OFFSET 和 LIMIT

我在 Postgres 9.3.4 中做了一個相當複雜的查詢：

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
             , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
    , LATERAL (
  SELECT *
  FROM   posts
  WHERE  source_id = s.source_id
  AND    deleted_at IS NULL
  ORDER  BY external_created_at DESC
  LIMIT  100
  ) p
ORDER  BY p.external_created_at DESC
LIMIT  100;

有關我的 Postgres 數據庫和設置等的更多詳細資訊，請參見此處。

這很好，除非我嘗試做一些分頁。

看看我的意思。當我進行計數時，沒有設置限制：

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
             , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
    , LATERAL (
  SELECT *
  FROM   posts
  WHERE  source_id = s.source_id
  AND    deleted_at IS NULL
  ORDER  BY external_created_at DESC
  ) p
ORDER  BY p.external_created_at DESC;

我得到(107 rows)，這是正確的結果數。

如果我想分頁，我會OFFSET在子查詢上使用。如果第一頁有 100 個文章，那麼第二頁應該有 7 個文章。

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
             , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
    , LATERAL (
  SELECT *
  FROM   posts
  WHERE  source_id = s.source_id
  AND    deleted_at IS NULL
  ORDER  BY external_created_at DESC
  LIMIT  100
  OFFSET 100
  ) p
ORDER  BY p.external_created_at DESC
LIMIT  100;

但相反，它返回(0 rows).

如果我對外部查詢進行分頁：

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
             , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
    , LATERAL (
  SELECT *
  FROM   posts
  WHERE  source_id = s.source_id
  AND    deleted_at IS NULL
  ORDER  BY external_created_at DESC
  ) p
ORDER  BY p.external_created_at DESC
LIMIT  100
OFFSET 100;

我知道(7 rows)哪個是正確的，但是查詢速度非常慢（請參閱此問題）

有沒有辦法正確地對這個查詢進行分頁，而不是減慢它，並得到正確的結果？

僅對於這兩頁，折衷方案可能是：

SELECT p.*
FROM   unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600
             , 17804, 20717, 27598, 27599}'::int[]) s(source_id)
    , LATERAL (
  SELECT *
  FROM   posts
  WHERE  source_id = s.source_id
  AND    deleted_at IS NULL
  ORDER  BY external_created_at DESC
  LIMIT  200   -- 100 + 100 (outer limit + offset) per source_id
  ) p
ORDER  BY p.external_created_at DESC
OFFSET 100
LIMIT  100;

這將比前 100 行慢得多，但仍比您在上一個問題中的原始查詢快得多。

在您的範例中總共只有 107 行，性能幾乎相同。

顯然，性能隨著每一頁的增加而下降。OFFSET一般來說，性能是一種痛苦。但是當與您的困難查詢結合使用時，它就變成了 PITA。

我會考慮使用預先計算的行號或更複雜（但也更有效）的解決方案的物化視圖：

• 空間索引能否幫助“範圍-按-限制”查詢

還有相關的：

• 使用來自許多表的列提高 order by 的性能

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