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

Question

在我的查询中，我只想调用符合确切where条件的数据。这些where条件是在索引中创建的。但explain显示了bit 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个项目。这样会更有效吗？

- Sha

Answer 1

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

回答问题所需的是，仅索引扫描是索引的扫描，它将提取相关数据而无需访问实际表格。这是因为相关数据已经在索引中。例如，考虑以下表：

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 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或其他值，运行时都将保持不变。如果是索引/仅索引扫描，则执行器将在获取由LIMIT子句指定的足够行数后停止扫描。对于位图堆扫描，这是不可能的。

- richyen