Re: PostgreSQL insert speed tests

Sezai YILMAZ <sezai.yilmaz@pro-g.com.tr> writes:
> Tom Lane wrote:
>> 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

I did some profiling and found that essentially all the slowdown as the
table gets larger is associated with searching the increasingly longer
hash chains to find free space for new index tuples.  The 7.3-to-7.4
slowdown you see must be due to some marginally slower code in
ReadBuffer.  Given the overall speedup at the more normal end of the
range, I'm not too concerned about that.

What this test basically shows is that a hash index is a loser for
indexing a column with only five distinct values.  Actually, any index
structure is a loser with only five distinct values; there is no case in
which it wouldn't be faster to just seqscan the table instead of using
the index.  If the test is accurately modeling your expected data
distribution, then you do not need the agentid and hostid indexes and
should get rid of them entirely.  The index on ownerid (200 distinct
values) is the only one that's marginally useful.

            regards, tom lane