Обсуждение: OT - Query for bit pattern

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?

--
Paul Tomblin <ptomblin@xcski.com> http://blog.xcski.com/
Revenge is an integral part of forgiving and forgetting.
               -- The BOFH

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

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

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

I have one lying around if you want. It's quite old, I think from circa 7.4 so
it may need some work to move it up to modern pg source. In particular it will
probably need to be adjusted for the packed varlena changes.

Consider it covered by the same license as Postgres.


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!