Re: Postgres 7.3.1 poor insert/update/search performance

Seth Robertson writes:

However, it looks like my hope to use arrays is
doomed though, I'm not sure I can handle the performance penalty.

Just in case I get the person who implemented arrays annoyed or
worried, I did not properly modify the "array" test and was vacuum'ing
the wrong table every 10000 vectors during the test. I realized that
this morning and the new array results are listed below. I also
experimented with batching read operations, and I was surprised to find
that this helps a great deal as well.

----------------------------------------------------------------------
"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 250 t/s (*)
"arrays with batching" Perl DBI 1020 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
"batched" Perl DBI read 3076 t/s (*)
"array" Perl DBI read 1754 t/s (*)
"batched array" Perl DBI read 2702 t/s (*)
"normal" Perl DBI for Oracle 314 t/s
"normal" Perl DBI read for Oracle 395 t/s
----------------------------------------------------------------------
(*) New/updated from this morning

This brings array code to within 11% of the performance of batched
non-arrays, and close enough to be an option. I may well be doing
something wrong with the server-side functions, but I don't see
anything quite so obviously wrong.

-Seth Robertson