strange index performance?

From: Thomas Finneid <tfinneid(at)fcon(dot)no>
To: pgsql-performance(at)postgresql(dot)org
Subject: strange index performance?
Date: 2009-01-24 10:57:21
Message-ID: 497AF411.1090504@fcon.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi

I just experienced a performance loss on inserts when redefining my
index on a table.

I have a database with the following table

table:
id1 int
id2 int
id3 int
id4 int
val1 float
....
tablespace dbspace

the data id distribution is hierarchical and even, well fitting to a
btree, there is about 20000 rows per insert (actually a jdbc COPY)

originally I had an index on all id felds

index:
btree on (id1, id2, id3, id4) tablespace indexspace

that gave me an insert performance of 230 ms

because my query does not need id2 I changed the index and removed id2
from the criteria:

index:
btree on (id1, id3, id4) tablespace indexspace

now an insert takes approx 330-430 ms

Anybody have any ideas why that is? I was expecting it to take
approximately the same amount of time or less, since there is an element
less in the criteria.

regards

thomas

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2009-01-24 14:57:10 Re: strange index performance?
Previous Message Thomas Finneid 2009-01-24 07:02:06 "tablespace" for tranaction log?