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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
