选择不同的，摆脱位图堆扫描
给定该表，
create table a (x int, y int);
create index a_x_y on a(x, y);
我希望像 select different x from a where y = 1 这样的查询仅使用索引，而不是使用索引按 y 进行过滤，然后执行位图堆扫描 获取 x 的所有值。
---------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=15.03..15.05 rows=2 width=4) (actual time=0.131..0.131 rows=0 loops=1)
-> Bitmap Heap Scan on a (cost=4.34..15.01 rows=11 width=4) (actual time=0.129..0.129 rows=0 loops=1)
Recheck Cond: (y = 1)
-> Bitmap Index Scan on a_x_y (cost=0.00..4.33 rows=11 width=0) (actual time=0.125..0.125 rows=0 loops=1)
Index Cond: (y = 1)
这种类型的查询需要什么样的索引？
Given the table
create table a (x int, y int);
create index a_x_y on a(x, y);
I would expect a query like select distinct x from a where y = 1 to use only the index, instead it uses the index to filter by y, then does a Bitmap Heap Scan to get all values of x.
---------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=15.03..15.05 rows=2 width=4) (actual time=0.131..0.131 rows=0 loops=1)
-> Bitmap Heap Scan on a (cost=4.34..15.01 rows=11 width=4) (actual time=0.129..0.129 rows=0 loops=1)
Recheck Cond: (y = 1)
-> Bitmap Index Scan on a_x_y (cost=0.00..4.33 rows=11 width=0) (actual time=0.125..0.125 rows=0 loops=1)
Index Cond: (y = 1)
What kind of index would be needed for this type of query?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
由于您要过滤索引的第二列，因此它不会用于直接索引扫描。如果您将索引更改为 y,x 而不是 x,y，它可能会为您提供所需的扫描。
此外，如果将实际数据放入表中，您很可能会得到不同的查询计划，因此您应该使用实际数据进行测试。
最后，我认为您误解了位图扫描节点。位图堆扫描并不意味着它正在进行实际的堆扫描。它使用索引来找出哪些页面上有有趣的行，然后在第二个操作中仅扫描表中的这些页面。
Since you're filtering on the second column of the index, it won't be used for a direct index scan. If you change the index to be on y,x instead of x,y, it might give you the scan you're looking for.
Also, you may very well get a different query plan if you put actual data in your table, so you should do your testing with realistic data.
Finally, I think you are misunderstanding the bitmap scan nodes. Bitmap Heap scan doesn't mean it's doing an actual heap scan. It's using the indexes to find out which pages there are interesting rows on, and will then scan those pages only in the table in the second operation.
位图堆扫描需要 0.129 毫秒，这还不够快吗？
如果您正在考虑“仅索引扫描”，PostgreSQL 还无法做到这一点。
The bitmap heap scan takes 0.129 milliseconds, isn't that fast enough?
If you are thinking about an "index only scan", PostgreSQL can not yet do that.