Re: OT - Query for bit pattern

Paul Tomblin <ptomblin@gmail.com> writes:
> If I have a bunch of data where one column in the table is a bitmap, is there
> an efficient way to do a query for all the records whose bitmap's first N bits
> matches a particular pattern?   Naively, I would store it as an int and do
> whatever the sql equivalent of '(hhcode & 0b11110000...) = 0b010100000...)'.
> But that is probably horribly inefficient.  Is there a better way?

One possibility is to change the bitmap to an integer array and use the
contrib/intarray module's GIST index support.  The intarray field will be
bulkier than the bitmap (though maybe not if your bitarrays are sparse),
but avoiding seqscan lookups might be worth it.

Or if you're willing to put in some work, you could make an index
opclass for bitmaps that works like intarray's does.

            regards, tom lane