PostgreSQL insert speed tests

#11

Sezai YILMAZ <sezai.yilmaz (AT) pro-g (DOT) com.tr> writes:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#12

Tom Lane wrote:

Quote:

Sezai YILMAZ <sezai.yilmaz (AT) pro-g (DOT) 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 (AT) candle (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

PostgreSQL insert speed tests

comp.databases.postgresql.general comp.databases.postgresql.general

Re: PostgreSQL insert speed tests - 03-01-2004 , 12:00 PM

Re: PostgreSQL insert speed tests - 03-03-2004 , 08:13 AM