Re: SQL Query Performance - what gives?

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.