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?

Community
  • 1
TheDude
  • 161
  • 1
  • 4
  • 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 00:48

1 Answers1

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.

Community
  • 1
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600