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

Re: Comparing user attributes with bitwise operators

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Patrick Clery <patrick(at)phpforhire(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Comparing user attributes with bitwise operators
Date: 2004-09-16 08:44:29
Message-ID: 87llfappqq.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-performance
Patrick Clery <patrick(at)phpforhire(dot)com> 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


In response to

Responses

pgsql-performance by date

Next:From: Greg StarkDate: 2004-09-16 08:53:49
Subject: Re: Comparing user attributes with bitwise operators
Previous:From: Christopher Kings-LynneDate: 2004-09-16 08:11:00
Subject: Re: Comparing user attributes with bitwise operators

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