Re: Performance of multi-column index on INSERT - Mailing list pgsql-performance

From Hannu Krosing
Subject Re: Performance of multi-column index on INSERT
Date
Msg-id 1039553422.1945.10.camel@rh72.home.ee
Whole thread Raw
In response to Performance of multi-column index on INSERT  (Ludwig Lim <lud_nowhere_man@yahoo.com>)
List pgsql-performance
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@postgresql.org
--
Hannu Krosing <hannu@tm.ee>

pgsql-performance by date:

Previous
From: Ludwig Lim
Date:
Subject: Performance of multi-column index on INSERT
Next
From: Hubert depesz Lubaczewski
Date:
Subject: Re: questions about disk configurations