PostgreSQL 如何使用 ORDER BY 和 LIMIT 1 優化查詢？

我有以下 PostgreSQL 架構：

CREATE TABLE User (
   ID INTEGER PRIMARY KEY
);

CREATE TABLE BOX (
   ID INTEGER PRIMARY KEY 
);

CREATE SEQUENCE seq_item;

CREATE TABLE Item (
   ID INTEGER PRIMARY KEY DEFAULT nextval('seq_item'),
   SENDER INTEGER REFERENCES User(id),
   RECEIVER INTEGER REFERENCES User(id),
   INFO TEXT,
   BOX_ID INTEGER REFERENCES Box(id) NOT NULL,
   ARRIVAL TIMESTAMP
);

它的主要案例是典型的生產者/消費者場景。不同的使用者可以在特定使用者的特定框中插入數據庫中的項目，並且每個使用者可以檢索發給她/他的框中的最頂部（這意味著最舊的）項目。它或多或少地模仿了數據庫級別的隊列功能。

更準確地說，最常見的操作如下：

INSERT INTO ITEM(SENDER, RECEIVER, INFO, BOX_ID, ARRIVAL) 
VALUES (nsid, nrid, ncontent, nqid, ntime);

並根據RECEIVER+SENDER或的組合檢索命令RECEIVER+BOX_ID：

SELECT * INTO it FROM Item i WHERE (i.RECEIVER=? OR i.RECEIVER is NULL) AND 
(i.BOX_ID=?) ORDER BY ARRIVAL LIMIT 1;
DELETE FROM Item i WHERE i.id=it.id;

和

SELECT * INTO it FROM Item i WHERE (i.RECEIVER=? OR i.RECEIVER is NULL) AND 
(i.SENDER=?) ORDER BY ARRIVAL LIMIT 1;
DELETE FROM Item i WHERE i.id=it.id;

最後兩個片段被打包在一個儲存過程中。

我想知道如何在給定這個案例的情況下實現最佳性能，並且知道使用者將在50,000和項目之間的某個位置插入和檢索（但是，數據庫永遠不會在給定點500,000包含超過項目）？100,000

編輯

對於沒有索引的語句，這是EXPLAIN我得到的：SELECT

Limit (cost=23.07..23.07 rows=1 width=35)
  -> Sort (cost=23.07..25.07 rows=799 width=35)
     Sort Key: ARRIVAL
     -> Seq Scan on Item i (cost=0.00..19.07 rows=799 width=35)
        Filter: (((RECEIVER = 1) OR (RECEIVER IS NULL)) AND (SENDER = 1))

根據我的理解，我得到的最好的結果EXPLAIN是當我在 time() 上放置一個索引時CREATE INDEX ind ON Item(ARRIVAL);：

Limit (cost=0.42..2.88 rows=1 width=35)
  -> Index Scan using ti on Item i (cost=0.42..5899.42 rows=2397 width=35)
     Filter: (((receiver = 2) OR (RECEIVER IS NULL)) AND (SENDER = 2))

在所有沒有索引的情況下，ARRIVAL我必須對錶格進行排序，這在我看來效率低下。如果我嘗試組合一個索引ARRIVAL，RECEIVER/SENDER我得到相同的解釋，但速度稍慢。

假設單個索引ARRIVAL是最有效的選擇是否正確？

上的 btree 索引(sender,arrival)可能會有所幫助。這將允許它直接跳轉到給定發件人的第一個到達的消息。

一個人(arrival,sender)不太可能有幫助。這使您可以全域跳轉到第一個發送的消息，但是您仍然必須沿著這些消息走，直到您從指定的發件人處找到一個。如果該特定發件人是新發件人，或者只向那些關注收件箱的人發送郵件，那麼您可能必須先瀏覽大部分索引，然後才能找到符合條件的郵件。這可能會有所幫助，因為您只需要遍歷索引，而不是索引和表的組合，但這仍然比正確的索引要小得多(sender,arrival)。

同樣，您需要另一個索引 on (box_id, arrival)，而不是 on (arrival, box_id)。

此外，如果實際表有 100,000 行，那麼對 800 行的表進行性能測試是沒有用的。

計劃者根據表中數​​據的分佈選擇使用索引。如果您有很多接收器，每個接收器只有幾個到達，它會更喜歡像 (receiver,arrival) 這樣的索引，如果所有到達都由少數使用者共享，則 (arrival,receiver) 索引將更有效率。確保在載入範例數據後對錶執行 ANALYZE。不要猶豫，創建兩個索引並在執行幾天后查閱 postgres 使用統計資訊 (pg_stat_all_indexes) 以確定哪些值得保留。

請重新考慮在您的查詢中使用 NULL 值作為發件人、收件人或郵箱。如果沒有收貨人或沒有箱子，物品記錄是什麼意思？一般來說，在外鍵約束上允許這樣做不是一個好主意。

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