我们有一个非常大的表，总行数围绕着~4 billion，每天的吞吐量大约是~20-25 million。
下面是示例表的定义。
table_name (
id bigint,
user_id bigint,
status smallint,
date timestamp,
.
.
some more columns
);
注意：状态是一个枚举，可以有6-7个可能的值。
下面是我们想要优化的查询：
SELECT *
FROM table_name
WHERE user_id = $user_id
AND status in (1, 2, 3, 6, 7, 10)
ORDER BY date
DESC LIMIT 20;
最初，我们在table_name (user_id, status)上使用index1。
由于该指数没有给出最佳性能。我们也想过把日期包括在索引中。
现在，我们已经尝试在表上创建一组不同的索引，但解释计划总是选择初始索引，即：index1。
以下是我们尝试的索引：
index2: table_name (user_id, status, date)
index3: table_name (user_id, status, date) where (status = ANY (ARRAY [1, 2, 3, 6, 7, 10]))
index4: table_name (user_id, status) where (status = ANY (ARRAY [1, 2, 3, 6, 7, 10]));
以下是解释分析输出：
Limit (cost=27029.07..27029.12 rows=20 width=251) (actual time=32.466..32.470 rows=20 loops=1)
-> Sort (cost=27029.07..27049.17 rows=8041 width=251) (actual time=32.465..32.467 rows=20 loops=1)
Sort Key: id DESC
Sort Method: top-N heapsort Memory: 38kB
-> Index Scan using index1 on table_name wt (cost=0.58..26815.10 rows=8041 width=251) (actual time=0.027..26.991 rows=37362 loops=1)
Index Cond: ((user_id = 111111) AND (status = ANY ('{1,3,2,7,6,10,8}'::integer[])))
Planning Time: 0.320 ms
Execution Time: 32.498 ms
我们的数据库postgres版本是：12.7，我们定期运行vaccuming。
我们想了解为什么其他索引没有用于我们的查询。
此外，考虑到我们的用例，是否有更好的方法来创建索引，以便我们能够在可接受的响应时间内为查询提供服务？
CREATE INDEX table_name_desc_index ON table_name (userid,_date DESC NULLS LAST);
然后尝试以下操作：
SELECT *
FROM table_name
inner join (values (1),(2),(3),(6),(7),(10)) val(v) ON (table_name.status = v )
WHERE user_id = $user_id
ORDER BY date
DESC LIMIT 20;
您选择的是*，因此由于*拖动的列数多于索引中的列数，您将无法获得仅索引扫描。您显示的其他索引(我可以看到(的唯一优点是启用仅索引扫描，因此，如果这不起作用，那么不选择使用这些索引也就不足为奇了。您可以通过将*更改为仅显示在索引中的列来测试这一理论，看看会发生什么。
关于你的一个索引：
(user_id, status, date) where (status = ANY (ARRAY [1, 2, 3, 6, 7, 10]))
这个指数似乎毫无意义。WHERE子句的优点在于它减少了"0"的不同限定值；"状态"；下至单个值("true"(。但随后将"；"状态"；进入索引体只会再次将它们分解。更好的索引是：
(user_id, date) where (status = ANY (ARRAY [1, 2, 3, 6, 7, 10]))
这个可以跳到特定user_id的末尾，向后扫描(以实现order by date desc(，一旦找到20行就停止。当你有"；"状态"；作为一个闯入者，它阻止它这么做。