Thread: 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!