如何优化这个简单而缓慢的查询

Question

我有一个相对简单的表，有几个列，其中两个是expires_at(Date)和museum_id(BIGINT, FOREIGN)。两者都进行了索引，也使用了复合索引。该表包含大约300万行。

运行这样简单的查询大约需要90秒才能完成：

SELECT * 
FROM external_users 
WHERE museum_id = 356 
AND ((expires_at > '2022-02-16 07:35:39.818117') OR expires_at IS NULL)

以下是解释分析输出：

Bitmap Heap Scan on external_users  (cost=2595.76..148500.40 rows=59259 width=1255) (actual time=4901.257..90786.702 rows=94272 loops=1)
Recheck Cond: (((museum_id = 356) AND (expires_at > '2022-02-16'::date)) OR ((museum_id = 356) AND (expires_at IS NULL)))
Rows Removed by Index Recheck: 391889
Heap Blocks: exact=34133 lossy=33698
->  BitmapOr  (cost=2595.76..2595.76 rows=63728 width=0) (actual time=4671.804..4671.806 rows=0 loops=1)
->  Bitmap Index Scan on index_external_users_on_museum_id_and_expires_at  (cost=0.00..2187.79 rows=54336 width=0) (actual time=1229.564..1229.564 rows=33671 loops=1)
Index Cond: ((museum_id = 356) AND (expires_at > '2022-02-16'::date))
->  Bitmap Index Scan on index_external_users_on_museum_id_and_expires_at  (cost=0.00..378.34 rows=9391 width=0) (actual time=3442.238..3442.238 rows=64337 loops=1)
Index Cond: ((museum_id = 356) AND (expires_at IS NULL))
Planning Time: 266.470 ms
Execution Time: 90838.777 ms

我在解释/分析输出中看不到任何有用的东西，但这可能与我缺乏这方面的经验有关。我的同行评审员也没有看到任何有趣的东西，这让我思考——我能做些什么来帮助postgres更快地处理这样的查询吗？还是只是像记录超过3M的表那样？

Answer 1

我将向您解释一些规则，以及优化此查询的方法。

1-在where条件下使用OR命令时，DB不能使用索引。建议使用union all。示例：

select * 
from external_users 
where museum_id = 356 
and expires_at > '2022-02-16 07:35:39.818117'
union all 
select * 
from external_users 
where museum_id = 356 
and expires_at is null

2-您的expires_at字段可能是时间戳类型。但是，date型比timestamp型更快。因为在timestamp类型中存储了小时、分钟、秒。此外，索引大小timestamp类型将大于date类型索引大小。如果需要存储完整的datetime，则可以使用类型转换。为了获得最佳性能，您必须创建一个基于函数的索引(在PostgreSQL上，这被称为表达式索引(，而不是标准索引。

select * 
from external_users 
where museum_id = 356 
and expires_at::date > '2022-02-16'
union all 
select * 
from external_users 
where museum_id = 356 
and expires_at is NULL 
/* 
We must cast `expires_at` field type to date type during creating the indexing process. Because in our query we use casting this type, so we must create an index via casting data.
*/
create index external_users_expires_at_idx 
ON external_users USING btree ((expires_at::date)); 

3-在where条件下，如果您总是同时使用两个、三个字段，建议为这些字段创建一个索引，但不要单独使用。在查询中，可能总是使用museum_id和expires_at字段。创建索引示例代码：

create index external_users_full_index on external_users  using btree (museum_id, (expires_at ::date));

所有这些方法中最重要的是第一条规则，因此不要使用OR命令。