Tom Lane wrote: > Sezai YILMAZ <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
