时间戳

首页 > 解决方案 > PostgreSQL 中的 Index-Only 和 Bitmap Index Scan 有什么区别？

postgresql - PostgreSQL 中的 Index-Only 和 Bitmap Index Scan 有什么区别？

问题描述

在我的查询中，我只想调用具有确切位置条件的数据。这些条件是在索引中创建的。但是解释显示位索引扫描。我不明白为什么。

我的查询如下所示：

Select 
r.spend,
r.date,
...
from metadata m 
inner join 
report r
on m.org_id = r.org_id and m.country_or_region = r.country_or_region and m.campaign_id = r.campaign_id and m.keyword_id = r.keyword_id  
where r.org_id = 1 and m.keyword_type = 'KEYWORD'
offset 0  limit 20

索引：

Metadata(org_id, keyword_type, country_or_region, campaign_id, keyword_id);
Report(org_id, country_or_region, campaign_id, keyword_id, date);

解释分析：

"Limit  (cost=811883.21..910327.87 rows=20 width=8) (actual time=18120.268..18235.831 rows=20 loops=1)"
"  ->  Gather  (cost=811883.21..2702020.67 rows=384 width=8) (actual time=18120.267..18235.791 rows=20 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Parallel Hash Join  (cost=810883.21..2700982.27 rows=160 width=8) (actual time=18103.440..18103.496 rows=14 loops=3)"
"              Hash Cond: (((r.country_or_region)::text = (m.country_or_region)::text) AND (r.campaign_id = m.campaign_id) AND (r.keyword_id = m.keyword_id))"
"              ->  Parallel Bitmap Heap Scan on report r  (cost=260773.11..2051875.83 rows=3939599 width=35) (actual time=552.601..8532.962 rows=3162553 loops=3)"
"                    Recheck Cond: (org_id = 479360)"
"                    Rows Removed by Index Recheck: 21"
"                    Heap Blocks: exact=20484 lossy=84350"
"                    ->  Bitmap Index Scan on idx_kr_org_date_camp  (cost=0.00..258409.35 rows=9455038 width=0) (actual time=539.329..539.329 rows=9487660 loops=1)"
"                          Index Cond: (org_id = 479360)"
"              ->  Parallel Hash  (cost=527278.08..527278.08 rows=938173 width=26) (actual time=7425.062..7425.062 rows=727133 loops=3)"
"                    Buckets: 65536  Batches: 64  Memory Usage: 2656kB"
"                    ->  Parallel Bitmap Heap Scan on metadata m  (cost=88007.61..527278.08 rows=938173 width=26) (actual time=1007.028..7119.233 rows=727133 loops=3)"
"                          Recheck Cond: ((org_id = 479360) AND ((keyword_type)::text = 'KEYWORD'::text))"
"                          Rows Removed by Index Recheck: 3"
"                          Heap Blocks: exact=14585 lossy=11054"
"                          ->  Bitmap Index Scan on idx_primary  (cost=0.00..87444.71 rows=2251615 width=0) (actual time=1014.631..1014.631 rows=2181399 loops=1)"
"                                Index Cond: ((org_id = 479360) AND ((keyword_type)::text = 'KEYWORD'::text))"
"Planning Time: 0.492 ms"
"Execution Time: 18235.879 ms"

在这里，我只想调用 20 个项目。应该更有效？

标签： postgresqlindexing

解决方案

位图索引扫描发生在结果集相对于搜索条件具有高选择性（即，满足搜索条件的行的百分比很高）时。在这种情况下，计划器将计划扫描整个索引，形成一个位图，其中包含磁盘上哪些页面可以从中提取数据（这发生在位图堆扫描步骤中）。这比顺序扫描要好，因为它只扫描磁盘上的相关页面，跳过它知道相关数据不存在的页面。根据优化器可用的统计信息，执行索引扫描或仅索引扫描可能没有优势，但它仍然比顺序扫描更好。

为了完成问题的答案，仅索引扫描是对索引的扫描，它将提取相关数据而无需访问实际表。这是因为相关数据已经在索引中。以这张表为例：

postgres=# create table foo (id int primary key, name text);
CREATE TABLE
postgres=# insert into foo values (generate_series(1,1000000),'foo');
INSERT 0 1000000

该表的列上有一个索引id，假设我们调用以下查询：

postgres=# EXPLAIN ANALYZE SELECT * FROM foo WHERE id < 100;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Scan using foo_pkey on foo  (cost=0.42..10.25 rows=104 width=8) (actual time=0.012..1.027 rows=99 loops=1)
   Index Cond: (id < 100)
 Planning Time: 0.190 ms
 Execution Time: 2.067 ms
(4 rows)

此查询会导致索引扫描，因为它会扫描索引以查找 id < 100 的行，然后访问磁盘上的实际表以提取查询*部分中包含的其他列SELECT。

但是，假设我们调用以下查询（注意SELECT id而不是SELECT *）：

postgres=# EXPLAIN ANALYZE SELECT id FROM foo WHERE id < 100;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Index Only Scan using foo_pkey on foo  (cost=0.42..10.25 rows=104 width=4) (actual time=0.019..0.996 rows=99 loops=1)
   Index Cond: (id < 100)
   Heap Fetches: 99
 Planning Time: 0.098 ms
 Execution Time: 1.980 ms
(5 rows)

这会导致仅索引扫描，因为仅id请求列，并且（自然）包含在索引中，因此无需访问磁盘上的实际表来检索其他任何内容。这节省了时间，但它的发生非常有限。

要回答您关于限制为 20 个结果的问题，限制发生在位图索引扫描发生之后，因此无论您限制为 20、40 还是其他值，运行时间仍然相同。在 Index/Index-Only Scan 的情况下，executor 将在获取足够多的行后停止扫描LIMIT。在您的情况下，使用位图堆扫描，这是不可能的

推荐阅读