Re: Indexing queries with bit masks

Mike Christensen <mike(at)kitchenpc(dot)com> writes:
> When a certain event happens, let's say event 4, I need to query for which
> users to notify. So I'll be doing something like:

> SELECT UserId FROM Users WHERE Subscriptions & 8;

> My question is say there's a million rows in the Users table. If I have an
> index on Subscriptions, will this index be used in the above query?

No. At least not with a standard btree index.

I'm not exactly sure that an index would be helpful at all --- it seems
like the selectivity of this condition won't be very good anyway, will
it? The more popular notifications will be subscribed to by a large
fraction of the user base. Maybe it'd be useful to index unpopular
notifications, but how often will you be searching for those?

regards, tom lane