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