postgresqlquery-optimizationdatabase-performance

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


Solution

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