用In子句选择查询的Postgres大表最优索引选择

Question

我们有一个非常大的表，在~4 billion周围有总行数，并且每天的吞吐量都在~20-25 million左右。

下面是示例表定义。

table_name (
   id bigint,
   user_id bigint,
   status  smallint,
   date    timestamp,
   .
   .
   some more columns
);

注意：status是一个枚举，可以有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;

最初，我们使用的是index1 on table_name (user_id, status)。

因为这个指数没有给出最佳的性能。我们还想把日期也包括在指数中。

现在，我们尝试在表上创建一组不同的索引，但是解释计划总是选择初始索引，即：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。

我们想了解为什么没有将其他索引用于查询。

此外，考虑到我们的用例，是否有更好的方法来创建索引，以便在可接受的响应时间内为查询提供服务？

Answer 1

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;

Answer 2

您正在选择*，因此您将无法获得仅索引扫描，因为*拖进的列比索引中的列多。您显示的其他索引(我可以看到)的唯一优点是启用仅索引的扫描，因此如果不能工作，则不选择使用这些索引也就不足为奇了。您可以通过将*更改为仅显示在索引中的列来测试这一理论，以查看发生了什么。

关于您的索引之一：

(user_id, status, date) where (status = ANY (ARRAY [1, 2, 3, 6, 7, 10]))

这个指数似乎毫无意义。WHERE子句的好处是，它将"status“的不同限定值降至单个值('true')。但是，将“状态”放入索引体，只会再次将它们重新分解。更好的指数是：

(user_id, date) where (status = ANY (ARRAY [1, 2, 3, 6, 7, 10]))

这个可以跳到特定user_id的末尾，向后扫描(以实现order by date desc)，并在找到20行后停止。当你有“身份”作为一个闯入者，它阻止它这样做。