Tom Lane wrote: > Merlin Moncure <mmonc...@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: karl.vcf>>
-- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
