加入聯結表以進行有效排序/分頁的推薦方法是什麼？

摘要：我有一個簡單的數據庫模式，但即使只有幾十萬條記錄，基本查詢的性能已經成為一個問題。

數據庫：PostgreSQL 9.6

簡化模式：

CREATE TABLE article (
 id bigint PRIMARY KEY,
 title text NOT NULL,
 score int NOT NULL
);
CREATE TABLE tag (
 id bigint PRIMARY KEY,
 name text NOT NULL
);
CREATE TABLE article_tag (
 article_id bigint NOT NULL REFERENCES article (id),
 tag_id bigint NOT NULL REFERENCES tag (id),
 PRIMARY KEY (article_id, tag_id)
);
CREATE INDEX ON article (score);

生產數據資訊：

所有表都是讀/寫的。低寫入量，每幾分鐘左右只有一個新記錄。

大約記錄數：

• ~66K 項目
• ~63K 標籤
• ~147K 文章標籤

每篇文章平均有 5 個標籤。

問題：我想創建一個視圖article_tags，其中包含每個文章記錄的標籤數組，可以通過article.score或不使用附加過濾來排序和分頁。

在我的第一次嘗試中，我驚訝地發現查詢需要大約 350 毫秒才能執行並且沒有使用索引。在隨後的嘗試中，我能夠將其降低到約 5 毫秒，但我不明白髮生了什麼。我希望所有這些查詢都花費相同的時間。我在這裡缺少什麼關鍵概念？

嘗試（SQL 小提琴）：

1. 多表連接（~350 ms），（如果按article.id排序，~5 ms！）——似乎是最自然的解決方案
2. 子查詢連接（~300 ms）——似乎也是一個自然的解決方案
3. 有限的子查詢連接（~5 ms）——超級尷尬，不能用於視圖
4. 橫向連接（~5 ms）——這真的是我應該使用的嗎？似乎是對橫向的濫用
5. ……還有什麼？

分頁

對於pagination，LIMIT(and OFFSET) 是簡單的，但對於較大的表格來說通常是低效的工具。你的測試**LIMIT 10只顯示了冰山一角**。無論您選擇哪種查詢，性能都會隨著 的增長而下降。OFFSET

如果您沒有或只有很少的並發寫訪問權限，則更好的解決方案是MATERIALIZED VIEW添加行號，並在其上加上索引。並且您的所有查詢都按行號選擇行。

在並發寫入負載下，這樣的 MV 很快就會過時（但CONCURRENTLY可以接受每 N 分鐘刷新一次 MV 之類的折衷方案）。

LIMIT/OFFSET根本無法正常工作，因為“下一頁”是那裡的移動目標，並且LIMIT/OFFSET無法應對。最好的技術取決於未公開的資訊。

有關的：

• 具有計數 b 樹的數據庫引擎以提供有效的分頁支持
• 在大表上使用 OFFSET 優化查詢

指數

您的索引通常看起來不錯。但是您的評論表明該表tag有很多行。通常，像這樣的表上的寫入負載非常少tag，這非常適合僅索引支持。所以添加一個多列（“覆蓋”）索引：

CREATE INDEX ON tag(id, name);

有關的：

• Postgres 可以對這個帶有連接表的查詢使用僅索引掃描嗎？

僅前 N 行

如果您實際上並不需要更多頁面（嚴格來說不是“分頁”），那麼任何查詢樣式都可以減少從相關表中檢索詳細資訊article 之前的合格行（昂貴）。您的“有限子查詢”（3.）和“橫向連接”（4.）解決方案很好。但你可以做得更好：

為變體使用**ARRAY建構子**LATERAL：

SELECT a.id, a.title, a.score, tags.names
FROM   article a
LEFT   JOIN LATERAL (
  SELECT ARRAY (
     SELECT t.name
     FROM   article_tag a_t 
     JOIN   tag t ON t.id = a_t.tag_id
     WHERE  a_t.article_id = a.id
  -- ORDER  BY t.id  -- optionally sort array elements
     )
 ) AS tags(names) ON true
ORDER  BY a.score DESC
LIMIT  10;

LATERAL子查詢一次​​組裝一個標籤*，* article_id因此是GROUP BY article_id多餘的，連接條件也是如此ON tags.article_id = article.id，並且基本ARRAY建構子比array_agg(tag.name)其餘簡單情況便宜。

• 為什麼 array_agg() 比非聚合 ARRAY() 建構子慢？

或者使用低相關性子查詢，通常更快，但是：

SELECT a.id, a.title, a.score
    , ARRAY (
        SELECT t.name
        FROM   article_tag a_t 
        JOIN   tag t ON t.id = a_t.tag_id
        WHERE  a_t.article_id = a.id
     -- ORDER  BY t.id  -- optionally sort array elements
     ) AS names
FROM   article a
ORDER  BY a.score DESC
LIMIT  10;

db<>fiddle here

SQL Fiddle

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