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

Re: PostgreSQL insert speed tests

From: Sezai YILMAZ <sezai(dot)yilmaz(at)pro-g(dot)com(dot)tr>
To: pgsql-general(at)postgresql(dot)org
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: PostgreSQL insert speed tests
Date: 2004-02-28 08:29:56
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
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 
>>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.



Attachment: schema.sql
Description: text/x-sql (626 bytes)
Attachment: tester.c
Description: text/x-c (3.7 KB)

In response to


pgsql-general by date

Next:From: Rajat KatyalDate: 2004-02-28 08:36:54
Subject: Re: value too long error
Previous:From: Uwe C. SchroederDate: 2004-02-28 07:16:42
Subject: Re: value too long error

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