Re: Planner question - "bit" data types

[Karl Denninger <karl@xxxxxxxxxxxxx>]

Tom Lane wrote:

Merlin Moncure <mmoncure@xxxxxxxxx> 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

begin:vcard
fn:Karl Denninger
n:Denninger;Karl
email;internet:karl@xxxxxxxxxxxxx
x-mozilla-html:TRUE
version:2.1
end:vcard

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance