sql - 使用 ORDER BY 和限制 1 优化 PostgreSQL 查询

Question

我有以下 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 是当我在时间上放置索引时(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 上的单个索引是最有效的选择是否正确？

Answer 1

关于索引，最好的方法是创建、测试您的查询并分析 EXPLAIN 计划。有时您创建了索引，而刨床甚至不使用它。测试一下就知道了。

默认获取主键索引，需要为引用的表创建索引

Postgres and Indexes on Foreign Keys and Primary Keys

并且您可以考虑使用 where 子句上的字段创建复合索引。

请注意，即使索引改进选择，也会对插入/更新产生影响，因为需要重建索引。

但是您必须再次测试每个更改，看看是否会改善您的结果。