Re: Indexing a Bit String column

George Oakman <oakmang(at)hotmail(dot)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