Re: Indexing queries with bit masks

On Fri, Apr 30, 2010 at 11:29 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Peter Hunsberger <peter(dot)hunsberger(at)gmail(dot)com> writes:
>> 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?
>
> My personal rule of thumb is that 10% is around the threshold where
> indexes stop being very helpful.  At that selectivity, you're going
> to be having to read every page of the table anyway, and it's not
> clear that the extra I/O to read the index is going to get repaid in
> CPU savings.  (Now if the table+index are fully cached in RAM, the
> threshold's probably a bit higher, but there still is not reason to
> think that an index is going to make for a huge improvement.)
>
>> If so, any answers to the OP's main question; what would be the most
>> efficient way to handle this type of thing?

Ok, that makes sense, which immediately makes me wonder if partitions
might make sense for this use case? In particular if there really are
only 10 different types?

[...]

> The best idea that comes to mind offhand is to not use an integer, but a
> boolean array, such that the queries look like
>
>        select ... where subscriptions[4];
>

Interesting idea. That might be worth testing for some of my use cases....

--
Peter Hunsberger