Indexing a Bit String column

[George Oakman <oakmang@xxxxxxxxxxx>]

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.

 

 

 

---

Share your photos with Windows Live Photos – Free Find out more!