Indexing a Bit String column - Mailing list pgsql-general

Hi all,

I am planning to use the Bit String data type for a large number of binary strings, e.g.

   CREATE TABLE myTable (myBitStringCol BIT(3));

I will need to perform & (bitwise AND) operations using SELECT on this column, e.g.

   SELECT * FROM myTable WHERE myBitStringCol & B'101' = myBitStringCol;

To optimise this type of SELECT statement, I guess I’ll have to build an index on the Bit String column, e.g.

   CREATE INDEX myBitStringCol_idx ON myTable (myBitStringCol);

 Is it all I need to do? Will PgSQL know how to index properly a Bit String column? Should I build the index using a special method, e.g.

   CREATE INDEX myBitStringCol_idx ON myTable USING gist(myBitStringCol);

Since we’re already talking of a Bit String column, the USING gist() statement looks a bit redundant to me. Basically, I though I would ask if I need to do anything special when indexing a BIT column.

Thanks for your comments.

George.