如何确定位图堆扫描和索引扫描？ [英] How Bitmap Heap Scan and Index Scan is decided?
问题描述
我正在测试不同的查询，我很好奇数据库如何决定使用位图堆扫描和索引扫描。
I'm testing different queries and I'm curious about how db decide using Bitmap Heap Scan and Index Scan.
创建索引客户的customers_email_idx（电子邮件
varchar_pattern_ops）;
create index customers_email_idx on customers(email varchar_pattern_ops);
正如您所见，有一个客户表（dellstore示例）和我在电子邮件列中添加索引。
As you can see there is a customers table (dellstore example) and I add an index to email column.
首先查询在这里：
select *来自客户的电子邮件，如'ITQ %@dell.com'; - >使用索引扫描查询
解释分析查询在此处：
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Scan using customers_email_idx on customers (cost=0.00..8.27 rows=2 width=268) (actual time=0.046..0.046 rows=0 loops=1)
Index Cond: (((email)::text ~>=~ 'ITQ'::text) AND ((email)::text ~<~ 'ITR'::text))
Filter: ((email)::text ~~ 'ITQ%@dell.com
'::text)
Total runtime: 0.113 ms
其他查询在此处：
从客户那里选择*，其中包含'IT％@ dell等电子邮件。 com公司; - >使用位图堆扫描查询
解释分析查询在此处：
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on customers (cost=4.54..106.77 rows=2 width=268) (actual time=0.206..0.206 rows=0 loops=1)
Filter: ((email)::text ~~ 'IT%@dell.com
'::text)
-> Bitmap Index Scan on customers_email_idx (cost=0.00..4.54 rows=29 width=0) (actual time=0.084..0.084 rows=28 loops=1)
Index Cond: (((email)::text ~>=~ 'IT'::text) AND ((email)::text ~<~ 'IU'::text))
Total runtime: 0.273 ms
你能解释这个例子为什么在这里使用Bitmap和Index Scan吗？
Can you explain this example why Bitmap and Index Scan is used here?
谢谢..
推荐答案
表中总共有多少行？决定是基于索引扫描将输出的行的比例。
How many rows do you have in total in the table? The decision is based on what proportion of the rows will be output by the index scan.
如果要访问的表的比例足够高，则位图索引scan用于确保尽可能多的磁盘访问是顺序的。相比之下，普通索引扫描对表数据进行一次一页的随机访问。 （如果预计要访问的表的比例足够高，则根本不使用索引，并且顺序加载整个表数据）
If a sufficiently high proportion of the table is going to be accessed, a bitmap index scan is used to ensure that as much of the disk access as possible is sequential. By contrast, a plain index scan does one-page-at-a-time random access to the table data. (And if the proportion of the table projected to be accessed is high enough, the index isn't used at all, and the entire table data loaded sequentially)
一个问题是从表中访问多少行的投影只是一个估计。但是你可以想象，'IT％'的匹配可能超过'ITQ％'（请记住，后缀不是索引扫描的一部分，只是最终的过滤器）
One issue is that the projection of how many rows from the table are going to be accessed is just an estimate. But as you can imagine, 'IT%' is likely to match more than 'ITQ%' (remember that the suffix isn't part of the index scan, only the final filter)
这篇关于如何确定位图堆扫描和索引扫描？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
