Skip site navigation (1) Skip section navigation (2)

Re: OT - Query for bit pattern

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Paul Tomblin <ptomblin(at)gmail(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: OT - Query for bit pattern
Date: 2008-03-28 02:11:04
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-jdbc
Paul Tomblin <ptomblin(at)gmail(dot)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

In response to


pgsql-jdbc by date

Next:From: Mats EkelundDate: 2008-03-28 08:08:35
Subject: Issue with DataBaseMetaData.GetTypeInfo()
Previous:From: Paul TomblinDate: 2008-03-28 01:59:27
Subject: OT - Query for bit pattern

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group