Re: [GENERAL] Re: Data warehousing

Stephen Davies wrote:

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

How do you think a database would find an intersection of two sets using
indexes? Say there were 3 clauses in the WHERE condition. The first
index lookup you do gets you all the records for that condition. Then
you would sequentially, for each result do a lookup on the second index.
For each of those results you would do a 3rd lookup.

So to get the first clause taken care of is 1 lookup. Lets say you get
50,000 results.
To take care of the 2nd lookup you do 50,000 index lookups. Lets say
there are 20,000 results.
To take care of the 3rd clause you do 20,000 lookups for a total of
70,001 index lookups. On the other hand if you had a single index on the
three attributes you would have 1 single index lookup.

I'm sure someone will correct me if I'm wrong, but that's my
understanding. BTW I have no idea if Postgres does or doesn't use
indexes for the secondary lookups, but I can't see that it would be a
huge difference anyway.

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

Of course it will work without any indexes, but making it fast is what
indexing is all about.