Chris,
> 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.
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.
...
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco