Karl Denninger wrote:
The bitmask allows the setting of multiple permissions but the table definition doesn't have to change (well, so long as the bits fit into a word!) Finally, this is a message forum - the actual code itself is template-driven and the bitmask permission structure is ALL OVER the templates; getting that out of there would be a really nasty rewrite, not to mention breaking the user (non-developer, but owner) extensibility of the current structure. Is there a way to TELL the planner how to deal with this, even if it makes the SQL non-portable or is a hack on the source mandatory?
You could maybe create function indexes for common bitmap operations; for example if it's common to check a single bit you could create 32 indexes, on (field & 1), (field & 2), (field & 4), etc. You could also maybe extend this so if you need to query multiple bits you decompose them into individual single-bit queries, e.g. instead of (field & 3) you do ((field & 1) and (field & 2)).
I suppose there will be a break-even point in complexity before which the above approach will be very slow but after it it should scale better then the alternative.
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
