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