Re: Planner question - "bit" data types

Tom Lane wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>
>> 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!
>>
>
> A possibly more useful variant is to treat the permission condition
> as a partial index's WHERE condition. The advantage of that is that
> the index's actual content can be some other column, so that you can
> combine the permission check with a second indexable test. The index
> is still available for queries that don't use the other column, but
> it's more useful for those that do.
>
> regards, tom lane
>
>
That doesn't help in this case as the returned set will typically be
quite large, with the condition typically being valid on anywhere from
10-80% of the returned tuples.

What I am trying to avoid is creating a boolean column for EACH
potential bit (and an index on each), as that makes the schema
non-portable for others and quite messy as well - while there are a
handful of "known masks" the system also has a number of "user defined"
bit positions that vary from installation to installation.

-- Karl