Re: Indexing queries with bit masks

Mike Christensen <mike@kitchenpc.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