Re: plans for bitmap indexes? - Mailing list pgsql-hackers

From Josh Berkus
Subject Re: plans for bitmap indexes?
Date
Msg-id 200410121304.22672.josh@agliodbs.com
Whole thread Raw
In response to plans for bitmap indexes?  (Yann Michel <yann-postgresql@spline.de>)
Responses Re: plans for bitmap indexes?
Re: plans for bitmap indexes?
Re: plans for bitmap indexes?
Re: plans for bitmap indexes?
List pgsql-hackers
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


pgsql-hackers by date:

Previous
From: "Joshua D. Drake"
Date:
Subject: Re: Required permissions for data directory
Next
From: "Dann Corbit"
Date:
Subject: Re: Hypothetical Indexes