5

I have been "googling" at least for an hour, but I was unable to find how to create a bitmap index in PostgreSQL, so my question is very simple: how to write this command (from Oracle) in PostgreSQL:

CREATE BITMAP INDEX name  ON table (column);
t0r0X
  • 4,212
  • 1
  • 38
  • 34
Baker
  • 425
  • 1
  • 7
  • 20
  • no, Postgres does not have it, but if you have very few possible values (and especially if they are very asymmetrically dispersed) consider trying `PARTIAL INDEX` – Vao Tsun Aug 20 '15 at 07:53
  • You can put for what purpose you want to create bitmat, refer this [documentation](http://www.postgresql.org/docs/9.3/static/indexes-bitmap-scans.html) which says bit map is created automatically by postgre (in memory) but not by any user. – MarmiK Aug 20 '15 at 08:17
  • have you tried reading the documentation? – Mehdi Aug 20 '15 at 08:20
  • 1
    to mef: I did, no answer found ... to Marmik: I was looking into explain plan for my query which was ideal for using of bitmap indexes but non was automatically created and sequence scan was used instead – Baker Aug 20 '15 at 08:31
  • If you have a slow query please read this: http://stackoverflow.com/tags/postgresql-performance/info and then post the relevant information. –  Aug 20 '15 at 09:04
  • to Vao Tsun: from what I have read from documentation, partial index is complete opposite to bitmap indexes .... "A major motivation for partial indexes is to avoid indexing common values. Since a query searching for a common value (one that accounts for more than a few percent of all the table rows) will not use the index anyway" – Baker Aug 20 '15 at 09:08
  • Related resource : https://wiki.postgresql.org/wiki/Bitmap_Indexes. – Bax Mar 08 '16 at 19:19

2 Answers2

3

The bitmap of pages is created dynamically for each query. It is not cached or re-used, and is discarded at the end of the bitmap index scan.

It doesn't make sense to create the page bitmap in advance because its contents depend on the query predicates.

Bitmap index create a separate bitmap (a sequence of 0 and 1) for each possible value of the column, where each bit corresponds to a string with an indexed value. Bitmap indexes are optimal for data where bit unique values (example, gender field)

PostgreSQL does not provide persistent bitmap index. But it can be used in database to combine multiple indexes. PostgreSQL scans each needed index and prepares a bitmap in memory giving the locations of table rows that are reported as matching that index’s conditions. The bitmaps are then ANDed and ORed together as needed by the query. Finally, the actual table rows are visited and returned.

Correcter
  • 3,466
  • 1
  • 16
  • 14
3

If you need, you can integrate roaring bitmaps into PostgreSQL. Roaring bitmaps are compressed bitmaps that tend to outperform conventional compressed bitmaps such as WAH, EWAH, or Concise.

You can use this repo to integrate roaring bitmaps. It contains an improved fork of Roaring bitmap implementation in greenplum-db.

dilanSachi
  • 562
  • 6
  • 14