我们使用 PostgreSQL 12 并有一个简单的表 ,event_participant
存储 100 GB 的数据。
event_participant
具有所有必要的索引,因此使用它们来获取所有行,即,不使用顺序扫描来获取任何行。
通常,它每秒获取 65 行,但有一天上午 10 点,我们运行了一项计划好的活动,其中使用索引扫描获取的行数跃升至每秒 540 万行。然而,索引扫描的数量保持不变,为每秒 200 次。表内容开始缓慢变化,但不足以触发自动分析,因为autovacuum_analyze_scale_factor
是表大小的 0.01 或 1%。
值得一提的是,我们在此数据库上配置了plan_cache_mode
TO ,因为我们的应用程序使用准备好的语句,并且我们希望避免由于实时活动而产生通用计划。force_custom_plan
经过 3 个小时的巨大 CPU 负载和索引扫描,我们手动执行了一次ANALYZE
,event_participant
索引扫描获取的活动行数立即从540 万行/秒下降到 450 行/秒。
我试图弄清楚该ANALYZE
命令如何影响索引扫描获取的活动行数,而索引扫描数保持不变。
更新 - 包括有关表结构和索引的更多详细信息。
> \d+ event_participant
Table "public.event_participant"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+------------------+-----------+----------+---------+----------+--------------+-------------
event_id | text | | not null | | extended | |
user_id | bigint | | not null | | plain | |
progress | text | | not null | | extended | |
level | integer | | not null | 0 | plain | |
quality | double precision | | | | plain | |
Indexes:
"event_participant_pkey" PRIMARY KEY, btree (user_id, event_id)
"event_participant_event_id_idx" btree (event_id)
Access method: heap
因此,上午 10 点,包含新事件的活动开始(新 event_id),并且该event_participant
表开始增长。每次用户登录时,后端应用程序知道哪些事件处于活动状态,并通过 user_id 和 event_id 选择所有条目:SELECT * from event_participant WHERE user_id=? AND event_id=?;
以获取用户的进度。
再次,自事件开始以来,
event_participant
表开始增长,但不足以触发autovacuum_analyze,
更新查询计划。在上午 10 点事件开始之前,
event_id=tour2023
表中不存在该事件,因此在数小时前发生的最新 autovacuum_analyze 期间,查询计划不知道tour2023
,因此它建议使用索引,event_participant_event_id_idx.
我通过运行使用不存在的 event_id 解释 SELECT;它使用在其上创建的索引,然后按 user_id 过滤行:这意味着在
tour2023
运行查询时事件启动后SELECT * from event_participant WHERE user_id=? AND event_id=?;
,PostgreSQL 用于event_participant_event_id_idx
获取所有行event_id=tour2023
,然后通过user_id
而不是使用复合索引来过滤所需的行"event_participant_pkey" PRIMARY KEY, btree (user_id, event_id)
。这导致索引扫描获取的行数增加,以及巨大的 CPU 使用率。手动运行后
ANALYZE
,查询计划被更新,数据库决定使用复合索引。因此,索引扫描获取的行数下降至 450 行/秒。使用现有 event_id 时解释输出:
所以,答案是查询计划已经过时,PostgreSQL 决定使用次优索引。
我仍然不明白 PostgreSQL 仅使用 (event_id) 索引的部分原因,因为我预计当在查询中同时指定 user_id 和 event_id 时,查询规划器会支持复合索引。