Tom Lane wrote:
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,
Yes I did. Go back and look at the archives. I provided full EXPLAIN and EXPLAIN ANALYZE results for the original query. Sheesh.
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
The point isn't portability to other SQL engines - it is to other people's installations. The bitmask is (since it requires only changing the mask constants in the container file that makes the SQL calls by reference) where explicit columns is not by a long shot.
In any event it looks like that's the only reasonable way to do this, so thanks (I think)
-- Karl