SELECT distinct, getting rid of Bitmap Heap Scan

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 开发者_C百科 (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?

---

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.

---

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.