6

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?

3
  • 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. Commented Jul 20, 2016 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. Commented Jul 20, 2016 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. Commented Jul 21, 2016 at 0:48

1 Answer 1

8

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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