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: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

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

I have a database with the following 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

    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:

    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.




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-2017 The PostgreSQL Global Development Group