Josh Berkus schrieb:
>>The most nearly comparable thing is be the notion of "partial
>>indexes," where, supposing you had 60 region codes (e.g. - 50 US
>>states, 10 Canadian provinces), you might set up indices thus:
>
> I'm afraid that you're mistaken about the functionality of bitmap indexes.
> The purpose of a bitmap index is not to partition an index, but to allow
> multiple indexes to be used in the same operation.
uh. sorry! In my first harsh replay I didn't know that. I thought you
wanted a new index type for binary images in BLOBS.
(just some hash, maybe optimized for image similarity)
> For example, imagine you have a table on a dating website with 18 columns
> representing 18 different characteristics for matching. Imagine that you
> index each of those columns seperately. If you do:
>
> SELECT * FROM people WHERE orientation = 'gay' AND gender = 'male' AND city =
> 'San Francisco';
>
> ... then the planner can use an index on orientation OR on gender OR on city,
> but not all three. Multicolumn indexes are no solution for this use case
> because you'd have to create a multicolumn index for each possible combo of
> two or three columns ( 18! ).
>
> The Bitmap index allows the query executor to use several indexes on the same
> operation, comparing them and selecting rows where they "overlap" like a Venn
> diagram.
--
Reini Urban
http://xarch.tu-graz.ac.at/home/rurban/