Re: Postgres 7.3.1 poor insert/update/search performance

In message <13165.1043188295@sss.pgh.pa.us>, Tom Lane writes:

    Seth Robertson <pgsql-performance@sysd.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