Re: PostgreSQL insert speed tests

Tom Lane wrote:

>Sezai YILMAZ <sezai(dot)yilmaz(at)pro-g(dot)com(dot)tr> writes:
>
>
>>I changed the three hash indexes to btree.
>>The performance is increased about 2 times (in PostgreSQL 7.3.4 1905
>>rows/s).
>>Concurrent inserts now work.
>>
>>
>Concurrent inserts should work with hash indexes in 7.4, though not 7.3.
>
>
I notice this condition. I do not get dead locks with 7.4 on schema with
hash indexes. 7.4 solves this problem but is very slow.

>The slowdown you report probably is due to the rewrite of hash indexing
>to allow more concurrency --- the locking algorithm is more complex than
>it used to be. I am surprised that the effect is so large though.
>Could you make your test program available?
>
>
The test program and .SQL script is attached

Comiple and link scenarios:

without transactions (where each insert is a transaction)
$ gcc -o tester tester.c -lpq

with default 400 inserts per transaction blocks
$ gcc -DTRANSACTION -o tester tester.c -lpq

with 200 inserts per transaction blocks
$ gcc -DTRANSACTION -DINSERTPERTRANSACTION=200 -o tester tester.c -lpq

I do concurrent tests by starting seperate tester programs from
different xterm windows.

>>Changed indexes are more suitable for hash type.
>>
>>
>
>Are they? How many distinct values are there in those columns?
>I suspect that your test may be stressing the case where only a few hash
>buckets are used and each bucket chain gets to be very long.
>
>
The biggest one gets 200 distinct values, the others are 5, and 10. More
information is in "tester.c" where INSERT query string is built.

Regards,

-sezai