postgresql - 为什么我对索引字段的计数查询很慢？

Question

我有以下架构:

leadgenie-django=> \d main_lead;
                Table "public.main_lead"
     Column      |           Type           | Modifiers
-----------------+--------------------------+-----------
 id              | uuid                     | not null
 body            | text                     | not null
 username        | character varying(255)   | not null
 link            | character varying(255)   | not null
 source          | character varying(10)    | not null
 keyword_matches | character varying(255)[] | not null
 json            | jsonb                    | not null
 created_at      | timestamp with time zone | not null
 updated_at      | timestamp with time zone | not null
 campaign_id     | uuid                     | not null
 is_accepted     | boolean                  |
 is_closed       | integer                  |
 raw_body        | text                     |
 accepted_at     | timestamp with time zone |
 closed_at       | timestamp with time zone |
 score           | double precision         |
Indexes:
    "main_lead_pkey" PRIMARY KEY, btree (id)
    "main_lead_campaign_id_75034b1f" btree (campaign_id)
Foreign-key constraints:
    "main_lead_campaign_id_75034b1f_fk_main_campaign_id" FOREIGN KEY (campaign_id) REFERENCES main_campaign(id) DEFERRABLE INITIALLY DEFERRED

如您所见，campaign_id 已编入索引。

当我使用 campaign_id 执行简单的 WHERE 时，查询仍然需要 16 秒。

leadgenie-django=> EXPLAIN ANALYZE select count(*) from main_lead where campaign_id = '9a183263-7a60-4ec0-a354-2175f8a2e5c9';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=202866.79..202866.80 rows=1 width=8) (actual time=16715.762..16715.763 rows=1 loops=1)
   ->  Seq Scan on main_lead  (cost=0.00..202189.94 rows=270739 width=0) (actual time=1143.886..16516.490 rows=279405 loops=1)
         Filter: (campaign_id = '9a183263-7a60-4ec0-a354-2175f8a2e5c9'::uuid)
         Rows Removed by Filter: 857300
 Planning time: 0.080 ms
 Execution time: 16715.807 ms

我原以为这个查询会很快(不到 1 秒)，因为这个字段已编入索引。我的期望是错误的吗？我可以做些什么来加快速度吗？

Answer 1

查询获取大约 25% 的表，因此 PostgreSQL 认为通过对整个表进行顺序扫描来完成此操作成本最低。这可能是正确的。

尝试运行

VACUUM main_lead;

这将更新可见性 map ，如果没有长时间运行的并发事务，那应该将大部分表 block 标记为全部可见，以便您可以获得更快的索引只扫描查询。