Patrick Clery <patrick@phpforhire.com> writes:
> Method 3 is the only one that used the index, but the only really acceptable
> method here is Method 1.
>
> My questions are...
> - Is there any hope in getting this to use an efficient index?
> - Any mathmaticians know if there is a way to reorder my bitwise comparison to
> have the operator use = and not an != (perhaps to force an index)? (AFAIK,
> the answer to the second question is no)
The only kind of index that is capable of indexing this type of data structure
for arbitrary searches would be a GiST index. I'm not aware of any
implementation for bitfields, though it would be an appropriate use.
What there is now is the contrib/intarray package. You would have to store
more than just the bitfields, you would have to store an array of integer
flags. That might be denser actually if you end up with many flags few of
which are set.
GiST indexes allow you to search arbitrary combinations of set and unset
flags. using the "@@" operator
int[] @@ query_int - returns TRUE if array satisfies query (like '1&(2|3)')
You might be able to look at the code there and adapt it to apply to bit
fields. If so I think it would be a useful tool. But GiST indexing is pretty
esoteric stuff.
--
greg