2

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?

  • 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ᵀᴹ Jul 20 '16 at 18:18
  • 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. – ypercubeᵀᴹ Jul 20 '16 at 18:24
  • 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. – Erwin Brandstetter Jul 21 '16 at 0:48
3

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:

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.

  • 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. – TheDude Jul 21 '16 at 13:52
  • @TheDude: Please ask a question with all relevant information. Follow instructions for Postgres performance questions here. Comments are not the place. – Erwin Brandstetter Jul 21 '16 at 19:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.