Patrick Clery <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend