PostgreSQL indexing on bit string

Question

What is the best way to create an index on a bit string column? Let's say I had the column of type bit(4) and I wanted to search for all entries that had had a specific bit set. So if I had the entries:

bitfield | ...
--------------
1001
1010
0110
0010
0000 

If I was trying to search for all entries that 0010 set, I can easily do that. But can I use indexes to optimize the searching?

• ypercubeᵀᴹ: Oh, it was perfectly clear what you are trying to do, never mind my "funny" comment. Why did you remove almost all the content? It was a much more clear question before.
• ypercubeᵀᴹ: My critique was for the denormalization / use of bit strings and that this path does not lead to optimal design and results. I'm happy to remove my comment.
• Erwin Brandstetter: Postgres has a large arsenal of indexing options and index access methods. The best solution depends on the complete situation. A question like this must provide context: Postgres version (!), table definition, cardinalities, typical queries, and whatever else may be of relevance.

Answer 1

First of all: it would be more efficient to store 4 bits of information as separate boolean columns - in every respect: easier to handle, easier to understand and change, easier to index, even smaller storage size! Closely related answer:

• Does it make sense to store a couple of Boolean values as array?

To answer your question asked: you can use a partial index like:

CREATE INDEX foo ON tbl (tbl_id)
WHERE bitfield & '0010' = '0010';

& being the bitwise AND operator.
tbl_id being the PK column. (The actual index column hardly matters here.)
In combination with a matching query:

SELECT *
FROM tbl
WHERE bitfield & '0010' = '0010';

But your question probably does not reflect your complete situation. There may be better options.

• TheDude: That was my initial implementation of indexing, but I was interested in seeing if there was a better way in doing so. Let's say that my bit string is 100 bits long and I wanted to be a be able to search for bit strings that had multiple bits set.
• Erwin Brandstetter: @TheDude: Please ask a question with all relevant information. Follow instructions for Postgres performance questions here. Comments are not the place.