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