Re: Indexing queries with bit masks

On Fri, Apr 30, 2010 at 10:08 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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?
>

We've got some similar columns (though nothing with any major number
of rows), so this is interesting...

If all subscriptions are roughly equal in popularity then any single
select should give ~ 10% of the data. That would seem to be selective
enough that you'd really want an index? If so, any answers to the
OP's main question; what would be the most efficient way to handle
this type of thing?

--
Peter Hunsberger