Re: Indexing a Bit String column

George Oakman <oakmang@hotmail.com> writes:

>  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);

No, the default will be to build a btree index which won't help these types of
queries at all.

You would want a GIST index if there was a built-in GIST opclass for these
kinds of queries, but sadly there isn't. You could add one fairly easily but
it would require C code. I think it would be a valuable addition to Postgres
if you do write one.

Note that something like "WHERE myBitStringCol & B'101'" might be selecting
too much of your table to make an index useful anyways. If each bit is set in
half the table then you're talking about selecting 3/4 of the table in which
case a full table scan would be more efficient than any index.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning