Skip site navigation (1) Skip section navigation (2)

Re: Performance of multi-column index on INSERT

From: Hannu Krosing <hannu(at)tm(dot)ee>
To: Ludwig Lim <lud_nowhere_man(at)yahoo(dot)com>
Cc: PostgreSQL Mailing List <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Performance of multi-column index on INSERT
Date: 2002-12-10 20:50:22
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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(at)postgresql(dot)org
Hannu Krosing <hannu(at)tm(dot)ee>

In response to

pgsql-performance by date

Next:From: Hubert depesz LubaczewskiDate: 2002-12-10 21:16:03
Subject: Re: questions about disk configurations
Previous:From: Ludwig LimDate: 2002-12-10 16:55:31
Subject: Performance of multi-column index on INSERT

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group