From: Greg Stark <gsstark@mit.edu>
To: Patrick Clery <patrick@phpforhire.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Comparing user attributes with bitwise operators
Date: 16 Sep 2004 04:44:29 -0400
Message-ID: <87llfappqq.fsf@stark.xeocode.com> (raw)
In-Reply-To: <200409160141.37604.patrick@phpforhire.com>
References: <200409160141.37604.patrick@phpforhire.com>
Patrick Clery <patrick@phpforhire.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsstark@mit.edu, patrick@phpforhire.com
Subject: Re: Comparing user attributes with bitwise operators
In-Reply-To: <87llfappqq.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox