Re: plans for bitmap indexes?

yann-postgresql(at)spline(dot)de (Yann Michel) writes:
> On Fri, Oct 08, 2004 at 10:09:18AM +0100, Dave Page wrote:
>> I think what Reini was asking was why do you think you need bitmap
>> indexes as opposed to any existing type?
>
> due to I'm developing a datawarehousing application we have lots of
> fact-data in our central fact-table. As I know how to improve
> performance on Oracle based datawarehouses, I'm used to add bitmap
> indexes for atributes having only a few distinct values. So I was
> looking for any comparable indexing technology but didn't find any
> so far.

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:

TABLE=my_table
FIELD=stateprov
FILE=$HOME/regionlist.txt
for region in `cat $FILE`; do
query="create index ${TABLE}_partial_on_${region} on $TABLE($FIELD) where $FIELD = '$region';"
echo $query | psql -d datawarehouse
done

That would set up 60 (or whatever $HOME/regionlist.txt indicated)
partial indices on the table on that field.

By the way, I thought ahead a little, in that; doing the same thing
for country codes might be as easy as replacing:

FIELD=country
FILE=$HOME/countrylist.txt

The partial indexes will not ALWAYS be useful; in cases where they
aren't, it is not inconceivable that there are improvements to be made
in the query optimizer...
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/linuxxian.html
A VAX is virtually a computer, but not quite.