Karl Denninger <karl@denninger.net> writes:
> Tom Lane wrote:
>> In that case you'd be wasting your time to get it to use an index
>> for the condition anyway. Maybe you need to take a step back and
>> look at the query as a whole rather than focus on this particular
>> condition.
> The query, sans this condition, is extremely fast and contains a LOT of
> other conditions (none of which cause trouble.)
> It is only attempting to filter the returned tuples on the permission
> bit(s) involved that cause trouble.
My comment stands: asking about how to use an index for this is the
wrong question.
You never showed us any EXPLAIN results, but I suspect what is happening
is that the planner thinks the "permission & mask = permission"
condition is fairly selective (offhand I think it'd default to
DEFAULT_EQ_SEL or 0.005) whereas the true selectivity per your prior
comment is only 0.1 to 0.8. This is causing it to change to a plan that
would be good for a small number of rows, when it should stick to a plan
that is good for a large number of rows.
So the right question is "how do I fix the bad selectivity estimate?".
Unfortunately there's no non-kluge answer. What I think I'd try is
wrapping the condition into a function, say
create function permission_match(perms int, mask int) returns bool
as $$begin return perms & mask = mask; end$$ language plpgsql
strict immutable;
The planner won't know what to make of "where permission_match(perms, 64)"
either, but the default selectivity estimate for a boolean function
is 0.333, much closer to what you need.
Or plan B, which I'd recommend, is to forget the mask business and go
over to a boolean column per permission flag. Then the planner would
actually have decent statistics about the flag selectivities, and the
queries would be a lot more readable too. Your objection that you'd
need an index per flag column is misguided --- at these selectivities
an index is really pointless. And I entirely fail to understand the
complaint about it being unportable; you think "&" is more portable than
boolean? Only one of those things is in the SQL standard.
regards, tom lane
