
> > 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:

> 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';

I think bitmap indexes do have valid use cases, but partitioned indexes
are really a wonderful feature with a lot of use cases, maybe including this one.

Workable examples for useful partitioned indexes, that help here are:

create index people_male_ix on people (city) where gender = 'male';
create index people_gay_ix on people (city) where orientation = 'gay';
create index people_male_gay_ix on people (city) where gender = 'male' and orientation = 'gay';

Note, that the indexed column differs from the partitioning clause.
Note also, that the last index will perform way better than a combo of bitmap indexes.

Andreas

Wed, 13 Oct 2004 10:24:07 +0200
"Zeugswetter Andreas DAZ SD" <ZeugswetterA@spardat.at>
Re: plans for bitmap indexes?
"Zeugswetter Andreas DAZ SD" <ZeugswetterA@spardat.at> writes:
> Workable examples for useful partitioned indexes, that help here are:

> create index people_male_ix on people (city) where gender = 'male';
> create index people_gay_ix on people (city) where orientation = 'gay';
> create index people_male_gay_ix on people (city) where gender = 'male' and orientation = 'gay';

> Note, that the indexed column differs from the partitioning clause.
> Note also, that the last index will perform way better than a combo of bitmap indexes.

This is definitely a useful technique in some cases, but it's got its
limits.  You have to have only a fairly small number of interesting
conditions (else the number of indexes gets out of hand) and those
conditions have to be spelled out explicitly in the query.  That is,
the last index will indeed work for
	SELECT * FROM people WHERE orientation = 'gay'
		AND gender = 'male' AND city = 'San Francisco';
but it will not work for
	SELECT * FROM people WHERE orientation = $1
		AND gender = $2 AND city = $3;
which is the sort of thing that the planner is increasingly going to
have to deal with.

Combining bitmaps at runtime is certainly somewhat more expensive to
execute, but it can deal with cases where the specific values being
searched for are not known until runtime.

			regards, tom lane

Wed, 13 Oct 2004 10:19:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: plans for bitmap indexes? 
Andreas,

> I think bitmap indexes do have valid use cases, but partitioned indexes
> are really a wonderful feature with a lot of use cases, 

Sure, no question.  That's why we have them.

> maybe including 
> this one.

Nope, not at all.

> Workable examples for useful partitioned indexes, that help here are:
>
> create index people_male_ix on people (city) where gender = 'male';
> create index people_gay_ix on people (city) where orientation = 'gay';
> create index people_male_gay_ix on people (city) where gender = 'male' and
> orientation = 'gay';

You've forgotten part of my premise (based on a real case I discussed on IRC) 
that there are EIGHTEEN criteria columns.   That would require, by the method 
you have above, roughly 18(3rd factorial) indexes, times the number of values 
allowed by each column, which if it averaged, say 5 values, would be 24,480 
indexes.   A little impractical, hmmm?   I think that might even break a 
system limit somewhere.

Tom,

> Note that what Josh is describing is not really a distinct index type,
> but a different way of using an index: that is, you pull candidate tuple
> locations from several indexes and intersect or union those sets before
> you go to the heap.  In principle this works whatever the index access
> methods are. 

Yes, exactly.    They're known as "bitmap" indexes because that's how Oracle 
implemented them, and AFAIK only Oracle currently has anything analogous.   
I'd personally be interested in any scheme that allowed the relatively 
efficient usage of multiple indexes on a single operation.

BTW, Tom, I was talking to Sean last night and he was saying that our current 
planner cost calculations assume that a 2-column index fetch will be twice as 
expensive as a 1-column index fetch.   Is this right?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Wed, 13 Oct 2004 10:05:48 -0700
Josh Berkus <josh@agliodbs.com>
Re: plans for bitmap indexes?
Josh Berkus <josh@agliodbs.com> writes:
> BTW, Tom, I was talking to Sean last night and he was saying that our
> current planner cost calculations assume that a 2-column index fetch
> will be twice as expensive as a 1-column index fetch.  Is this right?

No.

			regards, tom lane

Wed, 13 Oct 2004 14:03:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: plans for bitmap indexes? 