Re: Postgres 7.3.1 poor insert/update/search performance

In message <13165(dot)1043188295(at)sss(dot)pgh(dot)pa(dot)us>, Tom Lane writes:

Seth Robertson <pgsql-performance(at)sysd(dot)com> writes:
> I'll try that and report back later, but I was under the (false?)
> impression that it was primarily important when you had multiple
> database connections using the same table.

Definitely false. shared_buffers needs to be 1000 or so for
production-grade performance. There are varying schools of thought
about whether it's useful to raise it even higher, but in any case
64 is just a toy-installation setting.

Increasing the setting to 4096 improved write performance by 20%.
Increasing the setting to 8192 had no additional effect. I could try
a few more probes if anyone cared.

The quotes are important when you are dealing with BIGINT indexes.
You won't get an indexscan if the constant looks like int4 rather
than int8.

You are not kidding!!!! Changing this increased the search
performance to 2083 transactions/second. This is 30 times faster than
before, and 5 times faster than Oracle! Go Tom Lane!!!

Unfortunately, the update accidentally already used the quoting, so
this top did not directly help the write case. However, it did
inspire me to check some other suggestions I have read since obviously
performance was to be had.

----------------------------------------------------------------------
Oracle read performance: 395
Original read performance: 69
shared_buffer = 4096 118
+ quoted where (WHERE val = '5') 2083
----------------------------------------------------------------------

----------------------------------------------------------------------
Oracle write performance: 314
Original write performance: 39
shared_buffer = 4096: 47
+ Occassional (@ 10K & 60K vectors) vacuum analyze in bg: 121
+ Periodic (every 10K vectors) vacuum analyze in background: 124
+ wal_buffers = 24: 125
+ wal_method = fdatasync 127
+ wal_method = open_sync 248
+ wal_method = open_datasync Not Supported
+ fsync=false: 793
----------------------------------------------------------------------

Just to round out my report, using the fastest safe combination I was
able to find (open_sync *is* safe, isn't it?), I reran all 7
performance tests to see if there was any different using the
different access methods:

----------------------------------------------------------------------
"normal" C libpq 256 t/s
"normal" Perl DBI 251 t/s
"DBI Prepared Statement" Perl DBI 254 t/s
"Batching" Perl DBI 1149 t/s
"arrays" Perl DBI 43 t/s
"server-side function" Perl DBI 84 t/s
"server-side trigger" Perl DBI 84 t/s
"normal" Perl DBI read 1960 t/s
"normal" Perl DBI for Oracle 314 t/s
"normal" Perl DBI read for Oracle 395 t/s
----------------------------------------------------------------------

With a batching update of 1149 transactions per second (2900%
improvement), I am willing to call it a day unless anyone else has any
brilliant ideas. However, it looks like my hope to use arrays is
doomed though, I'm not sure I can handle the performance penalty.

-Seth Robertson