Re: PostgreSQL insert speed tests

Tom Lane wrote:
> Sezai YILMAZ <sezai(dot)yilmaz(at)pro-g(dot)com(dot)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.

This brings up whether we should have a "hint" mode that suggests
removing indexes on columns with only a few distinct values.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073