Re: Planner question - "bit" data types - Mailing list pgsql-performance

From Karl Denninger
Subject Re: Planner question - "bit" data types
Date
Msg-id 4AA2D390.9070702@denninger.net
Whole thread Raw
In response to Re: Planner question - "bit" data types  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Planner question - "bit" data types  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
Tom Lane wrote:
Merlin Moncure <mmoncure@gmail.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
Attachment

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Planner question - "bit" data types
Next
From: Tom Lane
Date:
Subject: Re: Planner question - "bit" data types