Re: Performance of multi-column index on INSERT

Ludwig Lim kirjutas T, 10.12.2002 kell 21:55:
> Hi:
>
> Is the performance overhead of creating a
> multi-column index greater than creating an individual
> index for each column? (i.e. Is the INSERT slower for
> a table with a three column index than a similar table
> with three single column indices?).
>
> I was wondering this because according to
> PostgreSQL Manual in the section on multi-columned
> indexes "Multicolumn indexes should be used sparingly.
> Most of the time, an index on a single column is
> sufficient and saves space and time".

You should create only as much indexes as you really need.

A multi-column index can not usually replace multiple single column
indexes and vice versa.

For example, while an index on a,b,c can be used for search on both a
and b it will not be used for search on b and c and will be used like
index on a for search on a and c.

While a multi-column index is slower than a single-column one, it is
definitely faster than multiple single column indexes - one 3-column
index should always be faster than 3 single-column indexes.

Also, currently even with multiple single-column indexes on a,b and c
the search on a and c will use only one index, either on a or c.

> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
--
Hannu Krosing <hannu(at)tm(dot)ee>