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?
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.