Re: [GENERAL] Re: Data warehousing

Chris Bitmead <chris(dot)bitmead(at)bigfoot(dot)com> wrote:
> Herouth Maoz wrote:
>
> > Maybe I'm missing the point here, but it seems to me that if
> > you simply use indices not as key definitions but as query
> > accelerators (as in "index the living daylights out of"), then
> > you may as well define a separate index on each and every
> > field. Why do multiple-field indices in such a case?
>
> Why do multi-field indexes? Umm. How about to avoid doing a sequential
> scan? If your query depends on multiple fields then once you've found
> the set based on the first index you would have to sequential scan that
> subset based on the second condition, unless you have a multi-field
> index.

Is this correct?

With most other relational databases databases that I have used, relational algebra
is used to intersect sets from each index to give the final set rather than just
stopping after the first index. Surely PostgreSQL could do the same.

The usual argument for multi-field indexes is that they are significantly
smaller than the equivalent multiple single indexes and also faster: iff the
fields you searching on are leading parts of the index.

Cheers,
Stephen Davies

========================================================================
Stephen Davies Consulting scldad(at)sdc(dot)com(dot)au
Adelaide, South Australia. Voice: 041-980 1417
Computing & Network solutions. Fax: 08-82728863