Re: PostgreSQL insert speed tests

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Sezai YILMAZ <sezai(dot)yilmaz(at)pro-g(dot)com(dot)tr>, pgsql-general(at)postgresql(dot)org
Subject: Re: PostgreSQL insert speed tests
Date: 2004-03-03 14:13:05
Message-ID: 200403031413.i23ED5B21712@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Mimi Siu 2004-03-03 14:14:00 Large Object
Previous Message Marc G. Fournier 2004-03-03 14:08:52 PostgreSQL 7.3.6 Now Available ...