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

Re: strange index performance?

From: Thomas Finneid <tfinneid(at)fcon(dot)no>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: strange index performance?
Date: 2009-01-25 08:14:45
Message-ID: 497C1F75.4080109@fcon.no (view raw or flat)
Thread:
Lists: pgsql-performance
Scott Marlowe wrote:
> Also, what other kind of usage patterns are going on.  

For this test there was nothing else going on, it was just that one 
writer. The complete usage pattern is that there is one writer that 
writes this data, about 20000 rows per second, and then a small number 
of readers that query for some data based on id1,3,4.
(To help you visualize it, think of a query where you want to know the 
names all residents on the 4th floor in that particular street 
independent of house number. So id1 would be the street, id2 would be 
the house number, id3 would be the floor number and id4 would be the 
apartment number. Such a query would only use id1,3,4)

> I wrote a
> simple test case for this and on a table with 100,000 entries already
> in it, then inserting 10,000 in a transaction and 10,000 outside of a
> transaction, I get insert rates of 0.1 ms and 0.5 ms respectively.
> With a table with 1,000,000 rows already in place, the insert times
> with all the fields in an index was 1.5ms and 4.3ms respectively.
> 
> With only i1, i3, i4, val1 in the index, the numbers for a table with
> 100,000 entries to start with was 0.1ms and 0.5 ms, just like the
> above with the larger index.  With a 1,000,000 initial table, inserts
> take 2.1 and 3.0 ms respectively.

How do you run that setup, because those times are amazing, my inserts 
take about 220ms, constantly from the first row in the table to the 1.2 
billionth row. The client I was using for the inserts is a bare-bone use 
case simulation tool I have written in java, to test different insert 
and query strategies for this application. Its using JDBC copy to do the 
inserts.

There is one thing you should know though, and that is that the real 
table I am using has 20 value fields where the 6 first fields contains a 
value, but that does not affect the difference int eh the execution time 
of the two indexes.

> So I don't think you've found the cause of your problem with the smaller index.

I dont quite understand what you are saying here, but I assume you are 
saying that the smaller index is not the cause of the increased insert time?

If so, I did the test with both indexes on exactly the same db and 
setup. And when the index uses all four ids the insert time is larger 
than if I only use id1,3,4.

What concerns me about your test, is that you dont seem to get constant 
insert times, so there is a difference between the two tests, which 
miuch be why you dont see the problem I am seeing with my index.

regards

thomas

In response to

Responses

pgsql-performance by date

Next:From: davidDate: 2009-01-25 08:36:28
Subject: Re: SSD performance
Previous:From: Thomas FinneidDate: 2009-01-25 07:45:05
Subject: Re: strange index performance?

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