Re: Planner question - "bit" data types

On Fri, Sep 4, 2009 at 6:29 PM, Josh Berkus<josh@agliodbs.com> wrote:
> Karl,
>
>> For reference, I was having SEVERE performance problems with the
>> following comparison in an SQL statement where "mask" was an integer:
>>
>> "select ... from .... where ...... and (permission & mask = permission)"
>
> AFAIK, the only way to use an index on these queries is through
> expression indexes.  That's why a lot of folks use INTARRAY instead; it
> comes with a GIN index type.
>
> It would probably be possible to create a new index type using GiST or
> GIN which indexed bitstrings automatically, but I don't know that anyone
> has done it yet.
>
> Changing your integer to a bitstring will not, to my knowledge, improve
> this.

agreed.   also, gist/gin is no free lunch, maintaining these type of
indexes is fairly expensive.

If you are only interested in one or a very small number of cases of
'permission', you can use an expression index to target constant
values:

"select ... from .... where ...... and (permission & mask = permission)"

create index foo_permission_xyz_idx on foo((64 & mask = 64));
select * from foo where 64 & mask = 64; --indexed!

this optimizes a _particular_ case of permission into a boolean based
index.  this can be a big win if the # of matching cases is very small
or you want to use this in a multi-column index.

merlin