Re: Bitmap index thoughts

mark(at)mark(dot)mielke(dot)cc wrote:
> On Wed, Dec 27, 2006 at 10:16:54PM +1100, Gavin Sherry wrote:
>> On Wed, 27 Dec 2006, Heikki Linnakangas wrote:
>>> But actually I'm not convinced we need to worry about efficient storage
>>> of small bitmaps at all. The typical use case for bitmap indexes is
>>> large tables with small number of distinct values, and the problem
>>> doesn't really arise in that scenario. Let's keep it simple for now, we
>>> can enhance it in later releases.
>> The scenario I'm concerned about is where a sales data base, say, has
>> 100,000 products. However, only 500 or 1000 products are popular. They
>> dominate, say >99% of the sales. The other 99,900 products consume a
>> little bit over 8K each for very little benefit :-(.
>> This is pretty contrived but it seem real world enough...
>
> Seems like a good candidate for CREATE INDEX WHERE :-)

Yeah, that was my first thought as well. However, in Gavin's example it
would be a nightmare to manually update the list popular products, and
recreate the index when it changes.

Something clever inside bitmap indexam would clearly be better.

But even in that scenario, 99000*8k pages ~= 750 megabytes of wasted
space might still be acceptable. Or not.

> I wonder what would happen if somebody implemented automatic index
> exclusion conditions after use of an INDEX proved to be in the realm
> of the worst case scenario? :-)

I'm sorry, I don't understand that sentence...

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com