Re: PostgreSQL insert speed tests

Tom Lane wrote:
> 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.

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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073