Re: strange index 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