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

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 (view raw or flat)
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

pgsql-performance by date

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

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